初识JDBC 如果我们要使用Java来操作数据库，由于数据库的种类繁多，并且sql语句并不完全相同，总而言之不同的数据库千差万别，这是不是意味着我们要对不同的数据库写不同的程序，这样的话对程序员的要求有点高，并且学习的成本也很大，所以我们要定义一套规范，要求对所有的数据库程序员只要写相同的程序就可以了。就相当于插座，我们定义好插座的标准，那么所有的商家必须按我的标准来，否则就不能使用插座。同理，在Java中定义好数据库的接口，定义接口中的方法有什么作用，而具体的实现细节则有数据库的厂商进行编写，程序员只要调用接口里面的方法就可以了。这里给出一个摘至维基百科的定义
Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity.
所以使用JDBC的好处便显而易见
程序员如果要开发访问数据库的程序，只需要会调用JDBC接口中的方法即可，不用关注类是如何实现的
使用同一套Java代码，进行少量的修改就可以访问其他JDBC支持的数据库
我们使用Java操作数据库，会使用到数据库的驱动，由各大数据库厂商提供，需要额外去下载，里面是对JDBC接口实现的类，导入驱动jar包的具体的步骤如下
在项目中新建一个libs文件夹
将jar包复制到这个文件夹中
选中这个jar包，右击找到"Add as Library"
上面是导入jar包的通用步骤，后面会经常导入jar包，所以需要熟记。
第一个JDBC程序 使用Java操作数据库一般包括下面几步
注册驱动
获得连接数据库的对象
获得执行sql语句的对象
执行sql语句
释放资源(断开连接)
程序如下(具体类后面解释)
import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;public class Demo01 public static void main (String[] args) throws Exception Class.forName("com.mysql.jdbc.Driver" ); Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1" , "root" , "root" ); String sql = "update account set balance = 2000 where id = 1" ; Statement statement = conn.createStatement(); statement.executeUpdate(sql); statement.close(); conn.close(); } }
核心类 下面就详细介绍JDBC的核心类(接口)以及它们的功能
DriverManager：驱动管理对象，功能如下
注册驱动：告诉程序该使用哪一个数据库驱动jar包
mysql5之后可以省略注册驱动的步骤，即不需要写Class.forName()
获取数据库连接
static Connection getConnection(String url, String user, String password)
url：连接的路径
格式：jdbc:mysql://ip地址(域名):端口号/数据库名称?参数=参数值
例子：jdbc:mysql://localhost:3306/db1
如果ip地址为本机地址且端口号为3306，则上面可省略ip地址和端口号，简写为：jdbc:mysql:///db1
如果出现乱码问题，可以指定参数?characterEncoding=utf8
user：用户名password：密码
Connection：数据库连接对象，相当于是在Java程序与数据库之间建立了一条通道，功能如下
获取执行sql的对象
Statement createStatement()PreparedStatement prepareStatement(String sql)
管理事务
开启事务：setAutoCommit(boolean autoCommit)，传入false即开启事务
提交事务：commit()
回滚事务：rollback()
Statement：执行sql的对象，含有如下方法
boolean execute(String sql)：可以执行任意的sql语句，不常用，了解即可int executeUpdate(String sql)
执行DML(insert、update、delete)语句、DDL(create，alter、drop)语句
返回值：影响的行数，可以通过这个影响的行数判断DML语句是否执行成功 返回值>0的则执行成功，反之，则失败。
ResultSet executeQuery(String sql)：执行DQL(select)语句
ResultSet：结果集对象，封装查询结果，数据库通过通道返回的结果
boolean next()
getXxx()
getXxx(int columnIndex)
getXxx(int columnLabel)
这里给出一个使用ResultSet的例子，比如我们要去查询account表
import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.Statement;public class Demo02 public static void main (String[] args) throws Exception Class.forName("com.mysql.jdbc.Driver" ); Connection conn = DriverManager.getConnection("jdbc:mysql:///db1" , "user" , "root" ); Statement statement = conn.createStatement(); String sql = "select * from account" ; ResultSet resultSet = statement.executeQuery(sql); while (resultSet.next()) { int id = resultSet.getInt("id" ); String name = resultSet.getString("name" ); double balance = resultSet.getDouble("balance" ); System.out.println(id + "---" + name + "---" + balance); } resultSet.close(); statement.close(); conn.close(); } }
JDBC工具类 其实上面的代码并不是十分的规范，因为资源的释放可能不能释放成功，因为前面发生异常就可以导致后面的程序执行不到，这样资源无法释放，就会导致内存越用越少(内存泄漏)，所以我们应当将资源释放的代码写到finally代码块中，如下
import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class Demo01 public static void main (String[] args) Connection conn = null ; Statement statement = null ; try { conn = DriverManager.getConnection("jdbc:mysql:///db1" , "root" , "root" ); statement = conn.createStatement(); String sql = "update account set balance = 2000 where id = 1" ; statement.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } finally { if (statement != null ) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null ) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
这时我们发现代码十分的冗长(特别是资源释放的部分)，并且每次写代码都会重复这些，再者程序所连接的数据库是写死的，如果需要改变的话还需要改动程序，我们可以将这些信息写在一个jdbc.properties的配置文件中，如果有改动直接改动配置文件即可
url=jdbc:mysql:///db1 user=root password=root driver=com.mysql.jdbc.Driver
将上面的这些操作抽离出来，写一个JDBC的工具类，在工具类中读取配置文件，以及抽离出一些方法，这样不用每次都写这么长的代码
import java.io.FileNotFoundException;import java.io.FileReader;import java.io.IOException;import java.net.URL;import java.sql.*;import java.util.Properties;public class JDBCUtils private static String url; private static String user; private static String password; private static String driver; static { try { Properties properties = new Properties(); ClassLoader classLoader = JDBCUtils.class.getClassLoader(); URL res = classLoader.getResource("jdbc.properties" ); String path = res.getPath(); properties.load(new FileReader(path)); url = properties.getProperty("url" ); user = properties.getProperty("user" ); password = properties.getProperty("password" ); driver = properties.getProperty("driver" ); Class.forName(driver); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection () throws SQLException return DriverManager.getConnection(url, user, password); } public static void close (Connection conn, Statement stam) if (stam != null ) { try { stam.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null ) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close (Connection conn, Statement stam, ResultSet resultSet) if (resultSet != null ) { try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } close(conn, stam); } }
用户登录练习 我们来写一个方法来判断用户输入的用户名和密码是否正确，思路就是根据用户输入的用户名和密码去数据库中查询，如果返回的ResultSet有内容，那么就确定输入正确，否则失败。假设有下面这么一个表(user)
+ | id | user | password | + | 1 | zhangsan | 123 | | 2 | lisi | 345 | +
下面是检查用户名和密码的方法
public static boolean login (String user, String password) Connection conn = null ; Statement statement = null ; ResultSet resultSet = null ; try { conn = JDBCUtils.getConnection(); statement = conn.createStatement(); String sql = "select * from user where user = '" + user + "' and password = '" + password + "'" ; resultSet = statement.executeQuery(sql); return resultSet.next(); } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.close(conn, statement, resultSet); } return false ; }
下面在main方法中写测试代码
public static void main (String[] args) Scanner scanner = new Scanner(System.in); System.out.println("请输入用户名：" ); String user = scanner.nextLine(); System.out.println("请输入密码：" ); String password = scanner.nextLine(); boolean flag = login(user, password); if (flag) { System.out.println("登录成功" ); } else { System.out.println("用户名或密码错误" ); } }
我们来试验一下
请输入用户名： lisi 请输入密码： 234 用户名或密码错误
请输入用户名： zhangsan 请输入密码： 123 登录成功
PreparedStatement PreparedStatement的作用同Statement，也是用来执行sql命令的，但是已经有了Statement，为什么还需要PreparedStatement呢? 那就要知道Statement存在什么问题，我们来看上面的用户登录程序，如果我们登录时这么写
请输入用户名： daad 请输入密码： a' or ' a' = ' a 登录成功
得到的结果居然登录成功了，因为sql语句被拼接成了
select * from user where user = 'daad' and password = 'a' or 'a' = 'a';
最后的or ‘a’ = ‘a’得到的永远是true，所以总是可以得到返回结果，所以自然会显示登录成功，这种情况叫做SQL注入。PreparedStatement正是为了解决这一个问题的，上面出现问题是因为我们的sql语句是拼接而成的，所以才会出现问题，PreparedStatement采取的办法是首先使用?占据位置(占位符)，然后对?所占据的位置进行赋值，赋值的方法为
setXxx()：接收两个参数
第一个参数表示为第几个占位符赋值，从1开始
第二个参数是值
所以我们将上面的登录代码修改如下
public static boolean login (String user, String password) Connection conn = null ; PreparedStatement preparedStatement = null ; ResultSet resultSet = null ; try { conn = JDBCUtils.getConnection(); String sql = "select * from user where user = ? and password = ?" ; preparedStatement = conn.prepareStatement(sql); preparedStatement.setString(1 , user); preparedStatement.setString(2 , password); resultSet = preparedStatement.executeQuery(); return resultSet.next(); } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.close(conn, preparedStatement, resultSet); } return false ; }
这时还是使用与上面相同的操作
请输入用户名： dadd 请输入密码： a or 'a' = 'a' 用户名或密码错误
在实际中我们一般使用PreparedStatement，因为它不仅可以防止SQL注入，并且效率更高。
JDBC事务 事务就是一组sql操作，这一组sql操作要么同时成功，要么同时失败。比如转账，一方钱的支出与另一方钱的到账必须同时成功或者同时失败，没有这里钱转出去了，另一方却没有到账的情况。有关事务一般包含下面三个操作
开启事务
setAutoCommit(false)开启事务后，直至提交，执行的sql语句不会在数据库中生效，而是会写在日志中，只有提交后才会将根据日志修改数据库中的数据
默认是自动提交的，即每次执行一次sql命令都会更改数据库中的内容，我们将自动提交关闭就相当于是开启了事务
提交事务
回滚事务
rollback()事务执行失败，这时我们就要回到执行事务前的状态，这时会把日志中的内容清空
一般在catch代码块中进行回滚操作
假设有下面这么一个表
+----+------+---------+ | id | NAME | balance | +----+------+---------+ | 1 | 张三 | 2000 | | 2 | 李四 | 1000 | +----+------+---------+
现在张三要给李四转500块钱
import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;public class Demo04 public static void main (String[] args) double money = 500 ; Connection connection = null ; PreparedStatement preparedStatement1 = null ; PreparedStatement preparedStatement2 = null ; try { connection = JDBCUtils.getConnection(); String sql1 = "update account set balance = balance - ? where id = ?" ; String sql2 = "update account set balance = balance + ? where id = ?" ; preparedStatement1 = connection.prepareStatement(sql1); preparedStatement1.setDouble(1 , money); preparedStatement1.setInt(2 , 1 ); preparedStatement2 = connection.prepareStatement(sql2); preparedStatement2.setDouble(1 , money); preparedStatement2.setInt(2 , 2 ); preparedStatement1.executeUpdate(); preparedStatement2.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.close(connection, preparedStatement1); JDBCUtils.close(null , preparedStatement2); } } }
执行上面的程序然后再次查表得
+----+------+---------+ | id | NAME | balance | +----+------+---------+ | 1 | 张三 | 1500 | | 2 | 李四 | 1500 | +----+------+---------+
但是如果我们在执行张三支出500块后手动添加一个异常，这时李四则不会收到500块
preparedStatement1.executeUpdate(); int i = 3 / 0 ;preparedStatement2.executeUpdate();
+----+------+---------+ | id | NAME | balance | +----+------+---------+ | 1 | 张三 | 1000 | | 2 | 李四 | 1500 | +----+------+---------+
张三的钱减少了500块，但是李四却没有收到。这就是问题，所以我们要开启事务，并且在catch代码块中进行回滚
import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;public class Demo04 public static void main (String[] args) double money = 500 ; Connection connection = null ; PreparedStatement preparedStatement1 = null ; PreparedStatement preparedStatement2 = null ; try { connection = JDBCUtils.getConnection(); connection.setAutoCommit(false ); String sql1 = "update account set balance = balance - ? where id = ?" ; String sql2 = "update account set balance = balance + ? where id = ?" ; preparedStatement1 = connection.prepareStatement(sql1); preparedStatement1.setDouble(1 , money); preparedStatement1.setInt(2 , 1 ); preparedStatement2 = connection.prepareStatement(sql2); preparedStatement2.setDouble(1 , money); preparedStatement2.setInt(2 , 2 ); preparedStatement1.executeUpdate(); int i = 3 / 0 ; preparedStatement2.executeUpdate(); connection.commit(); } catch (Exception e) { try { if (connection != null ) { connection.rollback(); } } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } finally { JDBCUtils.close(connection, preparedStatement1); JDBCUtils.close(null , preparedStatement2); } } }
这次再次执行
+----+------+---------+ | id | NAME | balance | +----+------+---------+ | 1 | 张三 | 1000 | | 2 | 李四 | 1500 | +----+------+---------+
虽然还是发生了异常，但是张三和李四并没有发生上面一方转出一方没有到账的情况，而是转账失败。
数据库连接池 每次我们使用完连接对象后都会将该对象销毁，然后下次需要连接对象时又需要重新创建。如果考虑到有频繁的操作数据库的操作，每次创建销毁的开销是很大的，所以就有了数据库连接池，里面有很多的连接对象，当我们需要时就从里面拿，用完之后不是销毁，而是将对象归还给连接池，这样做能够有效的提升程序的性能。Java定义了一个DataSource接口，我们可以通过该接口的getConnection()方法获取一个连接，并且可以通过close()方法归还这个连接(前提是这个连接是从连接池中获得的)。DataSource接口的实现我们不需要关心，我们只要知道如何使用即可，我们学习如何使用两种数据库连接池技术
C3P0Druid：阿里巴巴提供，目前最好的数据库连接池之一
C3P0 C3P0的使用步骤
导入jar包
c3p0-0.9.5.2.jarmchange-commons-java-0.2.12.jar
定义配置文件
只能为c3p0-config.xml或c3p0.properties，配置文件需放在src目录下
使用实现类ComboPooledDataSource获得连接池对象
下面给出配置文件c3p0-config.xml的内容
<c3p0-config > <default-config > <property name ="driverClass" > com.mysql.jdbc.Driver</property > <property name ="jdbcUrl" > jdbc:mysql:///db1</property > <property name ="user" > root</property > <property name ="password" > root</property > <property name ="initialPoolSize" > 5</property > <property name ="maxPoolSize" > 10</property > <property name ="checkoutTimeout" > 3000</property > </default-config > </c3p0-config >
下面给出一个使用的示例
import com.mchange.v2.c3p0.ComboPooledDataSource;import javax.sql.DataSource;import java.sql.Connection;import java.sql.SQLException;public class Demo05 public static void main (String[] args) throws SQLException DataSource ds = new ComboPooledDataSource(); Connection conn = ds.getConnection(); } }
Druid Druid的使用步骤
导入jar包
定义配置文件
使用工厂方法DruidDataSourceFactory.createDataSource()获得连接池
下面给出druid.properties的内容
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql:///db1 username=root password=root initialSize=5 maxActive=10 maxWait=3000
想必上面的各参数的含义不必解释，那么就给出一个使用Druid的例子
import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;import java.io.InputStream;import java.sql.Connection;import java.util.Properties;public class Demo06 public static void main (String[] args) throws Exception Properties pro = new Properties(); InputStream is = Demo06.class.getClassLoader().getResourceAsStream("druid.properties" ); pro.load(is); DataSource ds = DruidDataSourceFactory.createDataSource(pro); Connection conn = ds.getConnection(); } }
工具类 我们发现每次使用连接池时会有很多重复的操作，同上面我们应该写一个工具类JDBCUtils将一些操作抽离出来，JDBCUtils类如下
import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;import java.io.IOException;import java.sql.*;import java.util.Properties;public class JDBCUtils private static DataSource ds; static { Properties pro = new Properties(); try { pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties" )); ds = DruidDataSourceFactory.createDataSource(pro); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection () throws SQLException return ds.getConnection(); } public static void close (Connection conn, Statement stam) if (stam != null ) { try { stam.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null ) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close (Connection conn, Statement stam, ResultSet resultSet) if (resultSet != null ) { try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } close(conn, stam); } public static DataSource getDataSource () return ds; } }
Spring JDBC Spring对JDBC进行了简单的封装，提供了一个JdbcTemplate对象来简化JDBC的开发，使用步骤为
导入jar包
spring-beans-5.0.0.RELEASE.jarspring-core-5.0.0.RELEASE.jarspring-jdbc-5.0.0.RELEASE.jarspring-tx-5.0.0.RELEASE.jarcommons-logging-1.2.jar
创建JdbcTemplate对象
new JdbcTemplate(ds)：ds为数据库连接池对象
使用JdbcTemplate对象的方法对数据库进行操作
update()：执行DML语句
第一个参数为sql语句
第二个参数为可变参数，是占位符?所对应的值
queryForMap()：将结果封装为Map对象
只能查询一行数据，将结果封装为Map对象
将列名最为Key，将字段值作为Value
queryForList()：将结果封装为List对象
查询多行数据，每一行数据封装为一个Map对象，这些Map对象会被添加到一个List集合中返回
queryForObject()：将结果封装为对象
query()：将结果封装为JavaBean对象
下面就来做一个简单的演示，假设db1数据库中有一张表account其中内容如下
+----+------+---------+ | id | NAME | balance | +----+------+---------+ | 1 | 张三 | 1000 | | 2 | 李四 | 1000 | +----+------+---------+
update 现在我们使用update命令修改张三的balance为2000
import org.junit.Test;import org.springframework.jdbc.core.JdbcTemplate;public class Demo07 private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource()); @Test public void testUpdate () String sql = "update account set balance = 2000 where id = ?" ; template.update(sql, 1 ); } }
我们再次查询account表
+----+------+---------+ | id | NAME | balance | +----+------+---------+ | 1 | 张三 | 2000 | | 2 | 李四 | 1000 | +----+------+---------+
查询张三所在行(id = 1)，得到一个Map对象，我们将其打印出来
@Test public void testQueryForMap () String sql = "select * from account where id = ?" ; Map map = template.queryForMap(sql, 1 ); System.out.println(map); }
结果为
{id=1 , NAME=张三, balance=2000.0 }
queryForList 如果我们需要查询多条数据怎么办，这个时候就使用queryForList，它会将每条数据封装为Map集合，然后将这些Map集合添加到List集合中
@Test public void testQueryForList () String sql = "select * from account" ; List<Map<String, Object>> mapList = template.queryForList(sql); for (Map<String, Object> map: mapList) { System.out.println(map); } }
得到的结果为
{id=1 , NAME=张三, balance=2000.0 } {id=2 , NAME=李四, balance=1000.0 }
query 更多的时候我们希望将得到的结果封装为一个对象，现在我们新建一个类Account如下
public class Account private Integer id; private String name; private Double balance; public Integer getId () return id; } public void setId (Integer id) this .id = id; } public String getName () return name; } public void setName (String name) this .name = name; } public Double getBalance () return balance; } public void setBalance (Double balance) this .balance = balance; } @Override public String toString () return "Account{" + "id=" + id + ", name='" + name + '\'' + ", balance=" + balance + '}' ; } }
现在我们希望将获得结果封装为一个个Account对象，这时我们就需要使用query()方法了，query方法的第一个参数是sql语句，第二个参数是RowMapper<>的实现类，这个类我们可以自己实现，也可以使用Spring提供好的实现类，我们就使用Spring提供好的实现类BeanPropertyRowMapper<>，我们只要将Account的class属性传递进去即可
@Test public void testQuery () String sql = "select * from account" ; List<Account> list = template.query(sql, new BeanPropertyRowMapper<Account>(Account.class)); for (Account account : list) { System.out.println(account); } }
结果为
Account{id=1 , name='张三' , balance=2000.0 } Account{id=2 , name='李四' , balance=1000.0 }
queryForObject 现在我们来查询account表中右多少条数据，我们使用聚合函数count()，得到的结果是一个long类型的数字，我们使用queryForObject()查询，接收两个参数，第一个参数是sql命令，第二个参数是返回类型的class属性
@Test public void testQueryForObject () String sql = "select count(id) from account" ; Long num = template.queryForObject(sql, Long.class); System.out.println(num); }
结果为
微信
支付宝
