jdbc4.2 常用接口和类
DriverManager
static synchronized Connection getConnection(url, user, pass) //获取连接
Connection接口
//获取Statement才能执行SQL
Statement createStatement()
PreparedStatement prepareStatement(sql)
CallableStatement prepareCall(sql)
//控制事务的方法
Savepoint setSavepoint()
void setTransactionIsolation(level) //设置事务的隔离级别
void rollback() //回滚
void setAutoCommit(autoCommit) //关闭自动提交,打开事务
void commit() //提交
//java7 新增
setSchema(schema)
setNetworkTimeout()
Statement接口
ResultSet executeQuery(sql)
int executeUpdate(sql)
boolean execute(sql) //如果执行后第一个结果为ResultSet对象,返回true
statement.getResultSet()
statement.getUpdateCount()
statement.getMetaData() //获取结果集的元数据
PreparedStatement 是Statement的子接口,预编译的Statement对象
//效率更高、安全,防止SQL注入
void setXxx(parameterIndex, value)
//java8 新增
executeLargeUpdate() 影响记录数超过Integer.MAX_VALUE时用此方法
ResultSet 结果集对象
//移动指针、关闭
void close()
boolean absolute(row) 记录指针移动到row行
void beforeFirst() 首行之前,初始状态
boolean first()
boolean previous()
boolean next()
boolean last()
void afterLast()
//获取值
getXxx(columnIndex)
<T> T getObject(columnIndex, Class<T> type) 获取任意类型的值,java7新增
jdbc编程步骤
Class.forName("com.mysql.cj.jdbc.Driver");
try (
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false"
, "root", "123456");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from user;"))
{
while (resultSet.next()){
System.out.println(resultSet.getInt(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getString(3)
+ "\t" + resultSet.getString(4));
}
String insertSql = "insert into user(user_name, password, sex) values(?, ?, ?)";
PreparedStatement pStatement = connection.prepareStatement(insertSql);
pStatement.setString(1, "刘德华");
pStatement.setString(2, "1234444");
pStatement.setInt(3, 1);
pStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
可更新的结果集
//创建Statement时传入参数
resultSetType:
ResultSet.TYPE_FORWARD_ONLY jdk1.4之前默认值,只能向前移动
ResultSet.TYPE_SCROLL_INSENSITIVE 自由移动指针,底层数据改变不影响ResultSet内容
ResultSet.TYPE_SCROLL_SENSITIVE 自由移动指针,底层数据改变会影响
//需要底层数据库驱动的支持
resultSetConcurrency 并发模型
ResultSet.CONCUR_READ_ONLY 默认,只读模式
ResultSet.CONCUR_UPDATABLE 可更新的并发模式
//代码
statement = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
//可更新结果集需满足条件:
1. 所有数据来自一个表
2. 选出的数据集必须包含主键列
resultSet.updateString(2, 'aaa');
resultSet.updateRow(); //提交更改
处理Blob类型数据
Binary Long Object二进制长对象,保存图片、声音文件等大文件
插入数据:用PreparedStatement,方法setBinaryStream(parameterIndex, InputStream) 传入二进制输入流
获取数据:ResultSet的getBlob(columnIndex) 获取Blob对象,它的getBinaryStream()获取输入流,或getBytes()直接取出封装的二进制数据。
代码:
InputStream is = new FileInputStream(file);
preparedStatement.setBinaryStream(1, is, (int)file.length());
preparedStatement.execuateUpdate();
//获取
Blob imgBlob = rs.getBlob(1);
bytes[] rts = imgBlob.getBytes(1L, (int)imgBlob.length());
元数据分析
ResultSetMetaData分析结果集
DatabaseMetaData分析数据库信息
批量更新
代码:
boolean autoCommit = conn.getAutoCommit(); //保存之前的提交模式,后面用于恢复
conn.setAutoCommit(false); //开启事务支持
Statemet stmt = conn.createStatement();
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.executeBatch(); //java8 新增executeLargeBatch方法,需要驱动支持
conn.commit(); //提交
conn.setAutoCommit(autoCommit); //恢复
RowSet、离线RowSet
(略)
使用数据库连接池 C3P0:
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDrverClass("com.mysql.cj.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false");
dataSource.setUser("root");
dataSource.setPassword("123456");
dataSource.setMaxPoolSize(10);
dataSource.setMinPoolSize(1);
dataSource.setMaxStatements(50);
try (
Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from user;"))
{
while (resultSet.next()){
System.out.println(resultSet.getInt(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getString(3)
+ "\t" + resultSet.getString(4));
}
String insertSql = "insert into user(user_name, password, sex) values(?, ?, ?)";
PreparedStatement pStatement = connection.prepareStatement(insertSql);
pStatement.setString(1, "李四");
pStatement.setString(2, "1234444");
pStatement.setInt(3, 1);
pStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}