案例四：封装共有操作
封装一个数据库的会话的类
点击查看详细代码 import java.sql.*;
public class ConnectionUtil {
/**
* 获取连接对象的方法，返回一个Connection
* 方法体中是共有操作：加载驱动，建立连接
*/
public static Connection getConnection() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jdbctest?serverTimezone=GMT";
String user = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 关闭连接对象的方法，传入参数Connection对象
* 方法体中是共有操作：判断该对象是否为连接状态，是则关闭
*/
public static void close(Connection conn) {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭PreparedStatement对象的方法，传入参数为该对象
* 方法体中是共有操作：判断该对象是否为连接状态，是则关闭
*/
public static void close(PreparedStatement ps) {
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭结果集对象的方法，传入参数为ResultSet对象
* 方法体中是共有操作：判断该对象是否为连接状态，是则关闭
*/
public static void close(ResultSet rs) {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
封装对用户类的crud方法
点击查看详细代码 import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class UserManager {
/**
* 封装添加用户的方法
*/
public void add(User user) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = ConnectionUtil.getConnection();
String sql = "insert into t_user(username, age, sex, birthday) value(?, ?, ?, ?)";
ps = conn.prepareStatement(sql);
//setString 将指定的参数设置为给定的Java String值
ps.setString(1, user.getUsername()); // 第一个占位符传入的参数
ps.setInt(2, user.getAge());
ps.setString(3, user.getSex());
ps.setDate(4, new Date(user.getBirthday().getTime()), null);
int rows = ps.executeUpdate(); // 受影响行
System.out.println("->插入成功" + rows + "条信息");
} catch (Exception e) {
e.printStackTrace();
} finally {
ConnectionUtil.close(ps);
ConnectionUtil.close(conn);
}
}
/**
* 根据id删除用户
*/
public void del(int id) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = ConnectionUtil.getConnection();
String sql = "delete from t_user where id=?";
ps = conn.prepareCall(sql);
ps.setInt(1, id);
int i = ps.executeUpdate();
System.out.println("删除成功" + i + "条信息");
}catch (Exception e){
e.printStackTrace();
}finally {
ConnectionUtil.close(ps);
ConnectionUtil.close(conn);
}
}
/**
* 根据id查询用户信息
*/
public User getUser(int id) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = ConnectionUtil.getConnection();
String sql = "select * from t_user where id=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
rs = ps.executeQuery();
while(rs.next()) {
User u = new User();
// getInt 检索的当前行中指定列的值
u.setId(rs.getInt("id"));
// getString 检索的当前行中指定列的值
u.setUsername(rs.getString("username"));
u.setAge(rs.getInt("age"));
u.setBirthday(rs.getDate("birthday"));
u.setSex(rs.getString("sex"));
return u;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
ConnectionUtil.close(rs);
ConnectionUtil.close(ps);
ConnectionUtil.close(conn);
}
return null;
}
/**
* 查询所有用户的方法
*/
public List<User> getAll() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = ConnectionUtil.getConnection();
String sql = "select * from t_user";
ps = conn.prepareStatement(sql);
// 返回结果集
rs = ps.executeQuery();
List<User> lists = new ArrayList<User>();
while(rs.next()) {
User u = new User();
u.setId(rs.getInt("id"));
u.setUsername(rs.getString("username"));
u.setAge(rs.getInt("age"));
u.setBirthday(rs.getDate("birthday"));
u.setSex(rs.getString("sex"));
lists.add(u);
}
return lists;
} catch (Exception e) {
e.printStackTrace();
} finally {
ConnectionUtil.close(rs);
ConnectionUtil.close(ps);
ConnectionUtil.close(conn);
}
return null;
}
/**
* 更新用户信息的方法
*/
public int update(User user){
Connection conn = null;
PreparedStatement ps = null;
try {
conn = ConnectionUtil.getConnection();
String sql = "update t_user set age=?,username=?,sex=?,birthday=? where id=?";
ps = conn.prepareCall(sql);
ps.setInt(1, user.getAge());
ps.setString(2, user.getUsername());
ps.setString(3, user.getSex());
ps.setDate(4, new Date(user.getBirthday().getTime()), null);
ps.setInt(5, user.getId());
int i = ps.executeUpdate();
System.out.println("跟新成功" + i + "条信息");
}catch (Exception e){
e.printStackTrace();
}finally {
ConnectionUtil.close(ps);
ConnectionUtil.close(conn);
}
return 0;
}
}
测试
点击查看详细代码 import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
public class UserTest {
public static void main(String[] args) throws ParseException {
UserManager um = new UserManager();
User u = new User();
u.setUsername("大力");
u.setAge(20);
u.setSex("男");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
u.setBirthday(sdf.parse("1990-10-1"));
//插入一条数据
um.add(u);
//查询id为1的用户信息
User user = um.getUser(1);
System.out.println("id=1 ->" + user.getUsername());
//查询所有用户
List<User> list = um.getAll();
for(User u1 : list) {
System.out.println("User ->" + u1.getUsername());
}
//更新id为1的用户信息
User user1 = new User();
user1.setId(1);
user1.setUsername("狗剩");
user1.setAge(15);
user1.setSex("男");
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
user1.setBirthday(sdf1.parse("2021-1-1"));
um.update(user1);
//删除id为1的用户信息
um.del(1);
}
}
测试结果
->插入成功1条信息 id=1 ->小花 User ->小花 User ->大力 跟新成功1条信息 删除成功1条信息
jdbc操作mysql（二）
标签：cte mys zone delete out ati print 状态 finally
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did116951