1.概述
这篇文章主要说了JDBC的基本使用,包括Statement,PreparedStatement,JDBC的连接,Mysql创建用户创建数据表,C3P0的连接与配置,DBCP的连接与配置.
2.mysql的处理
这里的JDBC使用Mysql作为DBMS,请先安装Mysql,未安装的请点击这里下载,安装教程在这里,作者使用的Mysql的8.0.17版本.
(1)新建用户
随便新建一个用户,比如这里作者新建的是aa,密码是aa123bb.
create user 'aa'@'localhost' identified by 'aa123bb'
(2)建立数据表
建立测试用的数据表与数据库.
1create database db; 2use db; 3 4create table db 5( 6 id int PRIMARY key, 7 name char(20) 8);
(3)用户权限
对刚才新建的用户授权:
grant select,update,delete,insert on db.* to 'aa'@'localhost';
2.JDBC
(1)jar包
(2)连接
首先注册驱动,驱动需要一个url,用户名和密码,用户名和密码是上一步创建好的,url包含ip地址,端口和数据库的名字.
1private static final boolean mysqlVersionGreaterThen8 = true; 2private static final String driver = "com.mysql" + (mysqlVersionGreaterThen8 ? ".cj" : "") + ".jdbc.Driver"; 3private static final String ip = "127.0.0.1"; 4private static final String port = "3306"; 5private static String databaseName = "db"; 6private static String url; 7private static String username = "aa"; 8private static String password = "k041400r"; 9private static Connection connection = null; 10 11public static Connection getConnection() { 12 try { 13 url = "jdbc:mysql://" + ip + ":" + port + "/" + databaseName; 14 Class.forName(driver); 15 return connection = DriverManager.getConnection(url, username, password); 16 } catch (Exception e) { 17 e.printStackTrace(); 18 } 19 return null; 20}
这里要注意以下旧版本的mysql的驱动叫com.mysql.jdbc.Driver,新版本的叫com.mysql.cj.jdbc.Driver.还有就是url的格式:
jdbc:mysql://ip:port/database
(3)Statement
获取数据库连接后,使用createStatement方法创建Statement
- 对于select,使用Statement的executeQuery(sql),返回ResultSet
- 对于update,delete,insert,使用Statement的executeUpdate(sql)
其中sql是要执行的sql语句,一个String.
1public void useStatement() { 2 try { 3 useStatementInsert(); 4 useStatementSelect(); 5 useStatementUpdate(); 6 useStatementSelect(); 7 useStatementDelete(); 8 } catch (SQLException e) { 9 e.printStackTrace(); 10 } 11} 12 13public void useStatementInsert() throws SQLException { 14 String sql = "insert into db(id,name) values(1,'23')"; 15 Statement statement = connection.createStatement(); 16 statement.executeUpdate(sql); 17} 18 19public void useStatementDelete() throws SQLException { 20 String sql = "delete from db"; 21 Statement statement = connection.createStatement(); 22 statement.executeUpdate(sql); 23} 24 25public void useStatementSelect() throws SQLException { 26 String sql = "select * from db"; 27 Statement statement = connection.createStatement(); 28 ResultSet resultSet = statement.executeQuery(sql); 29 ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); 30 int count = resultSetMetaData.getColumnCount(); 31 while (resultSet.next()) { 32 for (int i = 1; i <= count; ++i) { 33 System.out.println(resultSet.getObject(i)); 34 } 35 } 36} 37 38public void useStatementUpdate() throws SQLException { 39 Statement statement = connection.createStatement(); 40 String sql = "update db set id = 3,name = '555' where id = 1"; 41 statement.executeUpdate(sql); 42}
这里对ResultSet使用的getMetaData,可以获取结果集的各种类型信息,包括字段的类型,个数,等等.
(4)PreparedStatement
PreparedStatement与Statement使用基本一样.调用的时候先使用Connection的prepareStatement(sql)创建,然后
-
对于select,使用executeQuery(),返回一个ResultSet
-
对于update,delete,insert使用executeUpdate().
public void usePrepareStatement() { try { usePrepareStatementInsert(); usePrepareStatementSelect(); usePrepareStatementUpdate(); usePrepareStatementSelect(); usePrepareStatementDelete(); } catch (SQLException e) { e.printStackTrace(); } }
public void usePrepareStatementInsert() throws SQLException { String sql = "insert into db(id,name) values(1,'23')"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.executeUpdate(); }
public void usePrepareStatementDelete() throws SQLException { String sql = "delete from db"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.executeUpdate(); }
public void usePrepareStatementSelect() throws SQLException { String sql = "select * from db"; PreparedStatement preparedStatement = connection.prepareStatement(sql); ResultSet resultSet = preparedStatement.executeQuery(); ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); int count = resultSetMetaData.getColumnCount(); while (resultSet.next()) { for (int i = 1; i <= count; ++i) System.out.println(resultSet.getObject(i)); } }
public void usePrepareStatementUpdate() throws SQLException { String sql = "update db set id = 3,name = '555' where id = 1"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.executeUpdate(); }
(5)事务
Connection有一个setAutoCommit()方法,把它设置成false即可关闭自动提交,所有语句准备好后,一次性使用commit()提交即可. 实现回滚可以配合SavePoint使用.
3.C3P0
(1)jar包
两个:
(2)配置文件
src下创建一个叫c3p0.properties的文件:
1c3p0.driverClass=com.mysql.cj.jdbc.Driver 2c3p0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/db 3c3p0.user=aa 4c3p0.password=aa123bb
这里按自己需要更改即可.
(3)工具类
1import com.mchange.v2.c3p0.ComboPooledDataSource; 2import java.sql.Connection; 3 4public class DbUtil 5{ 6 private static ComboPooledDataSource C3P0dataSource = new ComboPooledDataSource("c3p0.properties"); 7 public static void releaseConnection(Connection connection) 8 { 9 try 10 { 11 if(connection != null) 12 connection.close(); 13 } 14 catch (Exception e) 15 { 16 e.printStackTrace(); 17 } 18 } 19 20 public static Connection getC3P0Connection() 21 { 22 try 23 { 24 return C3P0dataSource.getConnection(); 25 } 26 catch (Exception e) 27 { 28 e.printStackTrace(); 29 } 30 return null; 31 } 32}
4.DBCP
(1)jar包
三个:
(2)配置文件
src下新建dbcp.properties:
1driver=com.mysql.cj.jdbc.Driver 2url=jdbc:mysql://127.0.0.1:3306/db 3username=aa 4password=k041400r 5initialSize=10 6maxActive=50 7maxIdle=15 8minIdle=10 9maxWait=60000 10connectionProperties=useUnicode=true;characterEncoding=utf8 11defaultAutoCommit=true
分别是驱动,url,用户名,密码,初始化连接数,最大连接数,最大空闲连接数,最小空闲连接数,最大等待实际,连接属性(这里设置了编码),自动提交.
(3)工具类
1import org.apache.commons.dbcp2.BasicDataSourceFactory; 2 3import java.io.InputStream; 4import java.sql.Connection; 5import java.util.Properties; 6import javax.sql.DataSource; 7 8public class DbUtil { 9 private static DataSource DBCPdataSource; 10 static { 11 try { 12 InputStream inputStream = DbUtil.class.getClassLoader().getResourceAsStream("dbcp.properties"); 13 Properties properties = new Properties(); 14 properties.load(inputStream); 15 DBCPdataSource = BasicDataSourceFactory.createDataSource(properties); 16 } catch (Exception e) { 17 e.printStackTrace(); 18 } 19 } 20 21 public static Connection getDBCPConnection() { 22 try { 23 return DBCPdataSource.getConnection(); 24 } catch (Exception e) { 25 e.printStackTrace(); 26 } 27 return null; 28 } 29 30 public static void releaseConnection(Connection connection) { 31 try { 32 if (connection != null) 33 connection.close(); 34 } catch (Exception e) { 35 e.printStackTrace(); 36 } 37 } 38}
首先加载属性文件,再使用Properties的load方法将其加载到一个Properties对象中,最后交给BasicDataSourceFactory处理.
5.源码
包含了jar包,配置文件,sql文件与测试代码.