JDBC学习(二)—将JDBC优化为工具包/数据库的增删改查
01 JDBC的优化
为了优化对JDBC的使用,避免代码的重复率,将创建JDBC的繁琐过程简化为工具包中的各个方法。具体步骤如下:
-
新建配置文件:内容包括创建连接时所需的驱动,数据库的用户名和密码,以及url
- 在src下建立db.properties,内容如下
driver = com.mysql.jdbc.Driver username = root password = 123456 url = jdbc:mysql://localhost:3306/jdbcstudy?useSSL=true
-
在src下新建包,这里命名为utils,在该包下新建类,这里命名为JDBCUtiles
- 注意事项:为了方便使用,工具包的成员变量和方法一般采用static修饰
package com.hooi.utils; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class JDBCUtils { public static String driver=null; public static String username=null; public static String password=null; public static String url=null; static { try { //以输入流的形式加载配置文件 InputStream stream = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(stream); //读取配置文件 driver = properties.getProperty("driver"); username = properties.getProperty("username"); password = properties.getProperty("password"); url = properties.getProperty("url"); //加载数据库驱动 Class.forName(driver); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } //获取数据库连接 public static Connection getConnect() throws SQLException { return DriverManager.getConnection(url,username,password); } //释放资源 public static void closeAll(ResultSet resultSet, Statement statement,Connection connection){ if (resultSet!=null){ try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if (statement!=null){ try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection!=null){ try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
02 数据库的增删改查
为了方便测试,这里引入Junit。Junit的导包十分方便,在IDEA类的编辑界面输入:@Test
,第一次使用Junit该语句会报红,按alt+enter选择add 'Junit4'to classpath
,IDEA会自动下载Junit并且导入lib目录
package com.hooi.demo;
import com.hooi.utils.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDemo {
@Test
public void insert() {
Connection connection = null;
Statement statement = null;
try {
//1.获取数据库连接
connection = JDBCUtils.getConnection();
//2.创建statement对象
statement = connection.createStatement();
//3.编写Sql语句
String sql = "INSERT INTO users(id,NAME,PASSWORD,email,birthday) VALUES(5,'wangwu','123456','wangwu@sina.com','1979-12-04');";
//4.执行sql语句
int i = statement.executeUpdate(sql); //返回受影响的行数
//5.查看结果
System.out.println("增加了"+i+"行数据");
} catch (SQLException e) {
e.printStackTrace();
} finally {
//5.释放资源
JDBCUtils.closeAll(null,statement,connection);
}
}
@Test
public void delete() {
Connection connection = null;
Statement statement = null;
try {
//1.获取数据库连接
connection = JDBCUtils.getConnection();
//2.创建statement对象
statement = connection.createStatement();
//3.编写Sql语句
String sql = "delete from users where id = 4";
//4.执行sql语句
int i = statement.executeUpdate(sql); //返回受影响的行数
//5.查看结果
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6.释放资源
JDBCUtils.closeAll(null,statement,connection);
}
}
@Test
public void update() {
Connection connection = null;
Statement statement = null;
try {
//1.获取数据库连接
connection = JDBCUtils.getConnection();
//2.创建statement对象
statement = connection.createStatement();
//3.编写Sql语句
String sql = "update users set name = 'lily' where id = 4";
//4.执行sql语句
int i = statement.executeUpdate(sql); //返回受影响的行数
//5.查看结果
if (i>0){
System.out.println("修改成功");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6.释放资源
JDBCUtils.closeAll(null,statement,connection);
}
}
@Test
public void query() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//1.获取数据库连接
connection = JDBCUtils.getConnection();
//2.创建statement对象
statement = connection.createStatement();
//3.编写Sql语句
String sql = "select * from users";
//4.执行sql语句
resultSet = statement.executeQuery(sql);
//5.查看结果
while (resultSet.next()){
System.out.println(resultSet.getInt("id"));
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getString("password"));
System.out.println(resultSet.getString("email"));
System.out.println(resultSet.getDate("birthday"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6.释放资源
JDBCUtils.closeAll(resultSet,statement,connection);
}
}
}