JDBC 工具类 Ⅳ
4.工具类优化
4.1 准备db.properties
# 文件所在位置是src目录下
driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/javaee2011?useSSL=false
username=root
password=123456
4.2 使用Properties读取配置文件操作
private static String jdbcUrl;
private static String username;
private static String password;
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream("./src/db.properties"));
jdbcUrl = properties.getProperty("jdbcUrl");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(properties.getProperty("driverClass"));
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
5. update方法
5.1 update方法分析
执行SQL语句流程 核心类 PreparedStatement
1. 获取数据库连接对象
2. 准备SQL语句
3. 根据SQL语句,预处理操作,得到PreparedStatement对象
4. 给予PreparedStatement对象 SQL语句对应参数
5. 执行SQL语句
6. 关闭资源
针对update delete insert
不同的地方:
1. SQL语句不同
2. 对应SQL语句参数不同
不同点就是当前update 通用方法的参数,一个是目标执行SQL语句,一个是对应SQL语句的参数列表
SQL ==> String类型参数
SQL语句的参数列表 ==> Object... 不定长参数,执行任意数据类型
5.2 update方法实现
@Test
public void testUpdate() {
Connection connection = null;
PreparedStatement statement = null;
connection = JdbcUtil.getConnection();
String sql = "update javaee2011.student set name = ?, age = ? where id = ?";
try {
statement = connection.prepareStatement(sql);
statement.setObject(1, "ctrl");
statement.setObject(2, 60);
statement.setObject(3, 2);
statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(connection, statement);
}
}
5.3 其余常用方法
@Test
public void testInsert() {
Connection connection = null;
PreparedStatement statement = null;
connection = JdbcUtil.getConnection();
String sql = "insert into javaee2011.student(id, name, age, gender, score, info) values(?,?,?,?,?,?)";
try {
statement = connection.prepareStatement(sql);
statement.setObject(1, 11);
statement.setObject(2, 22);
statement.setObject(3, 33);
statement.setObject(4, 44);
statement.setObject(5, 55);
statement.setObject(6, 66);
int i = statement.executeUpdate();
System.out.println(i);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(connection, statement);
}
}
@Test
public void testDelete() {
Connection connection = null;
PreparedStatement statement = null;
connection = JdbcUtil.getConnection();
String sql = "delete from javaee2011.student where id = ?";
try {
statement = connection.prepareStatement(sql);
statement.setObject(1, 2);
statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(connection, statement);
}
}
@Test
public void testSelectOne() {
ResultSet resultSet = null;
Connection connection = null;
PreparedStatement statement = null;
connection = JdbcUtil.getConnection();
String sql = "select * from javaee2011.student where id = ?";
try {
statement = connection.prepareStatement(sql);
statement.setObject(1, 3);
resultSet = statement.executeQuery();
Student student = null;
if (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
boolean gender = resultSet.getBoolean("gender");
int score = resultSet.getInt("score");
String info = resultSet.getString("info");
student = new Student(id, name, age, gender, score, info);
}
System.out.println(student);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(connection, statement, resultSet);
}
}
@Test
public void testSelectAll() {
ResultSet resultSet = null;
Connection connection = null;
PreparedStatement statement = null;
connection = JdbcUtil.getConnection();
String sql = "select * from javaee2011.student";
try {
statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
ArrayList<Student> list = new ArrayList<>();
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
boolean gender = resultSet.getBoolean("gender");
int score = resultSet.getInt("score");
String info = resultSet.getString("info");
list.add(new Student(id, name, age, gender, score, info));
}
for (Student student : list) {
System.out.println(student);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(connection, statement, resultSet);
}
}
ps. 码边闲话
这样子无限重复式的写代码,不仅繁琐而且浪费时间,最主要的是容易掉头发。
所以,我们把内容重复的代码封装起来,以后需要的时候可以随时调用。