1 获取连接
1.1 方式一：硬编码
- 基本参数
- 加载驱动
- 获取连接
public void connection1() {//mysql可以省略注册驱动
//1. 基本参数
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/jdbc_test?useSSL=false&serverTimezone=UTC";
String user = "root";
String password = "root";
try {
//2. 加载驱动
Class.forName(driverName);
//3. 获取连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
} catch (Exception e) {
e.printStackTrace();
}
}
1.2 方式二：通过读取properties
- 读取.properties
- 获取参数
- 加载驱动
- 获取连接
public void connection2(){//将参数提取到.properties中
//1. 读取.properties
InputStream inputStream = ConnectionTest.class.getClassLoader().getResourceAsStream("JDBC.properties");
Properties info = new Properties();
try {
info.load(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
//2. 获取参数
String driverName = info.getProperty("driverName");
String url = info.getProperty("url");
String user = info.getProperty("user");
String password = info.getProperty("password");
try {
//3. 加载驱动
Class.forName(driverName);
//4. 获取连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
} catch (Exception e) {
e.printStackTrace();
}
}
1.3 封装JDBCUtils
- 获取连接：同方式二
- 关闭连接：关闭所打开的连接
public class JDBCUtils {
public static Connection getConnection(){
//1. 读取.properties
InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("JDBC.properties");
Properties info = new Properties();
try {
info.load(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
// 2. 获取参数
String driverName = info.getProperty("driverName");
String user = info.getProperty("user");
String password = info.getProperty("password");
String url = info.getProperty("url");
//3. 加载驱动
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
//4. 获取连接
Connection connection = DriverManager.getConnection(url, user, password);
//5. 返回连接对象
return connection;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(Connection connection, PreparedStatement preparedStatement){
close(connection, preparedStatement, null);
}
public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
try {
connection.close();
preparedStatement.close();
if(resultSet!=null){
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2. 操作数据
PS：为了代码清晰简洁易看（懒得写），所有异常全部抛出，记得一定要自己try-catch
2.1 修改数据（增、删、改）
- 获取连接
- 预编译
- 填充占位符
- 执行
- 关闭连接
public void alter(String sql,Object ...args) throws SQLException {
//1. 获取连接
Connection connection = JDBCUtils.getConnection();
//2. 预编译
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//3. 填充占位符
if (args!=null){
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1, args[i]);
}
}
//4. 执行
preparedStatement.execute();
//5. 关闭连接
JDBCUtils.close(connection, preparedStatement);
}
2.2 查询数据
- 获取连接
- 预编译
- 填充占位符
- 执行（获取结果集）
- 获取元数据
- 封装为对象
- 返回对象
public <T> T select(Class<T> clazz,String sql,Object ...args) throws Exception {
//1. 获取连接
Connection connection = JDBCUtils.getConnection();
//2. 预编译
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//3. 填充占位符
if(args!=null){
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1, args[i]);
}
}
//4. 执行（获取结果集）
ResultSet resultSet = preparedStatement.executeQuery();
//5. 得到元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//6. 封装为对象
T t = null;
int columnCount = metaData.getColumnCount();
if(resultSet.next()){//游标
t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
String columnLabel = metaData.getColumnLabel(i + 1);
Object columnValue = resultSet.getObject(i + 1);
Field declaredField = clazz.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(t,columnValue);
}
}
//7. 关闭连接
JDBCUtils.close(connection, preparedStatement,resultSet);
//8. 返回对象
return t;
}
2.3 批处理
-
将每次执行改为按批执行
- 加入Batch
- 执行Batch
- 清空Batch
-
提升效率可以将自动提交改为手动提交
public void insertBatchTest() throws SQLException {
long startTime = System.currentTimeMillis(); //计时开始
//1. 获取连接
Connection connection = JDBCUtils.getConnection();
connection.setAutoCommit(false); //关闭自动提交
//2. 预编译
String sql = "insert into `user_info` (`user`) value (?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for(int i = 0; i =< 5000; i++){
//3. 填充占位符
preparedStatement.setString(1, '1');
//4. 执行
//4.1 加入Batch
preparedStatement.addBatch();
if(i % 500 == 0){
//4.2 执行Batch
preparedStatement.executeBatch();
//4.3 清空Batch
preparedStatement.clearBatch();
}
}
connection.commit(); //手动提交
//5. 关闭连接
JDBCUtils.close(connection, preparedStatement);
long endTime = System.currentTimeMillis(); //计时结束
System.out.println(endTime-startTime); //打印耗时
}
3 事务
自动提交
- DDL
- 默认情况下的DML
- 断开连接
- 关闭自动提交（setAutoCommit）
- 成功后提交 （commit）
- 失败后回滚（rollback）
public void transactionTest() {
Connection connection = JDBCUtils.getConnection();
PreparedStatement preparedStatement1 = null;
PreparedStatement preparedStatement2 = null;
//张三给李四转100
try {
//1. 关闭自动提交
connection.setAutoCommit(false);
String sql1 = "UPDATE `user_info` SET `balance`=`balance`-100 WHERE `user`='zhangsan';";
preparedStatement1 = connection.prepareStatement(sql1);
preparedStatement1.execute();
// 模拟出错
//int i = 3/0;
String sql2 = "UPDATE `user_info` SET `balance`=`balance`+100 WHERE `user`='lisi';";
preparedStatement2 = connection.prepareStatement(sql2);
preparedStatement2.execute();
//2, 手动提交
connection.commit();
System.out.println("交易成功");
} catch (Exception e) {
e.printStackTrace();
try {
//3. 回滚
connection.rollback();
System.out.println("交易失败，回滚");
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
try {
connection.setAutoCommit(true);
if (connection!=null){
connection.close();
}
if (preparedStatement1!=null){
preparedStatement1.close();
}
if (preparedStatement2!=null){
preparedStatement2.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
网友评论