JDBC 增、删、改、查

增、删、改方法

public class JdbcTest {

/**
* 提供： 增 、删、改3个功能的通用方法
*
* @param sql
* @param args sql 中占位符的值，可以用多个逗号隔开
*/
public void update(String sql, Object... args) throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;

try {
connection = JdbcUtils.getConnection();
System.out.println(sql);
preparedStatement = connection.prepareStatement(sql);

for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.release(preparedStatement, connection, null);
}
}

@Test
public void insert() throws Exception {
String sql = "INSERT INTO user(user_name,sex,user_role,password,id_card,register_time)"
+ " VALUES(?,?,?,?,?,?)";

update(sql,"小红","女","VIP用户","xh","654...","2019-01-25 14:02:03");
}

@Test
public void delete() throws Exception {
// 删除 user 表中 user_name 为小红的信息
String sql = "DELETE FROM user WHERE user_name=?";
update(sql,"小红");
}

@Test
public void modify() throws Exception {
// user 表中 user_name 为小红 的用户，将其 user_role 改为 普通用户
String sql = "UPDATE user set user_role=? WHERE user_name=?";
update(sql,"普通用户","小红");
}
}
通用查询方法封装

    利用面向对象编程的思想，我们新建了一个 User 类，此类变量为 user 表对应的列名，代码如下：

package com.xww;

public class User {

public String userName;
public String password;
public String registerTime;
public String sex;
public String userRole;
public String idCard;

public User(String userName, String password, String registerTime,
String sex, String userRole, String idCard) {
super();
this.userName = userName;
this.sex = sex;
this.userRole = userRole;
this.password = password;
this.idCard = idCard;
this.registerTime = registerTime;
}

public String getUserName() {
return userName;
}

public void setUserName(String userName) {
this.userName = userName;
}

public String getSex() {
return sex;
}

public void setSex(String sex) {
this.sex = sex;
}

public String getUserRole() {
return userRole;
}

public void setUserRole(String userRole) {
this.userRole = userRole;
}

public String getPassword() {
return password;
}

public void setPassword(String password) {
this.password = password;
}

public String getIdCard() {
return idCard;
}

public void setIdCard(String idCard) {
this.idCard = idCard;
}

public String getRegisterTime() {
return registerTime;
}

public void setRegisterTime(String registerTime) {
this.registerTime = registerTime;
}

@Override
public String toString() {
return "User [userName=" + userName + ", password=" + password + ", registerTime=" + registerTime + ", sex="
+ sex + ", userRole=" + userRole + ", idCard=" + idCard + "]";
}
}
    那么查询 user 表的方法就可以写为这样（不够灵活）：

public User query(String sql) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
User user = null;

try {
connection = JdbcUtils.getConnection();
System.out.println(sql);
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery(http://www.amjmh.com);

while (resultSet.next()) {
user = new User(resultSet.getString(2), resultSet.getString(3), resultSet.getString(4),
resultSet.getString(5), resultSet.getString(6), resultSet.getString(7));
System.out.println(user.toString());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.release(preparedStatement, connection, resultSet);
}
return user;
}

 

---------------------