今天通过JDBC对数据库进行增删改查操作，并在控制台打印出来操作结果
首先建立一个数据库和一张表
数据库名为db_java7,表名为tb_user
表结构如下：
下面就对这张表进行数据的增删改查
建立一个实体类User，映射表中字段
代码如下：
public class User {
//1.对属性的封装
private int id; //用户id
private String username; //用户名
private String password; //用户密码
//2.get and set
public int getId() {
return id;
}
public String getUsername() {
return username;
}
public String getPassword() {
return password;
}
public void setId(int id) {
this.id = id;
}
public void setUsername(String username) {
this.username = username;
}
public void setPassword(String password) {
this.password = password;
}
//构造方法
public User(int id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
public User(String username, String password) {
this.username = username;
this.password = password;
}
public User() {
}
//4.toString()重写
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
其中除对属性的封装需要手动输入外，其他方法皆可用右键编辑界面选择Generate来自动生成。
建立工具类DBUtil
public class DBUtil {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/db_java7?useSSL=true&characterEncoding=utf-8";
private static String user = "root";
private static String password = "123";
//1.获取驱动
static {
//初始化时这里会执行，注册驱动
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//2.创建连接
public static Connection get_conn() throws SQLException {
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println("数据库连接成功" + conn);
return conn;
}
//3.关闭连接
public static void get_CloseConn(ResultSet rs,PreparedStatement pstm,Connection conn) throws SQLException {
if(rs != null){
rs.close();
}
if(pstm != null){
pstm.close();
}
if(conn != null){
conn.close();
}
}
}
创建测试类TestUser
大体结构如下所示
public class TestUser2 {
Connection conn = null; //用来获取连接
PreparedStatement pstm = null; //存放sql语句的对象
ResultSet rs = null; //查询结果集
Scanner sc = new Scanner(System.in); //输入对象
public void addUser(User user){...} //测试增加
public void delUser(User user){...} //测试删除
public void modifyUser(User user){...} //测试修改
public void queryUser(User user){...} //测试查询
public static void menu(){...} //菜单
public static void main(String args[]){
menu()
}
}
其中以addUser()为例，经过获取连接、获取存放sql语句的对象、补全sql语句缺省、执行sql并获得结果、处理结果、关闭连接等几个阶段，实现了数据库的增加功能，具体代码如下：
public void addUser(User user){
try {
//1.获取连接
conn = DBUtil.get_conn();
//2.获取存放sql语句的对象
pstm = conn.prepareStatement("insert into tb_user(username,password) values(?,?)");
//3.填坑
System.out.println("待增加用户名：");
user.setUsername(sc.nextLine());
System.out.println("待增加用户密码：");
user.setPassword(sc.nextLine());
pstm.setString(1,user.getUsername());
pstm.setString(2,user.getPassword());
//4.执行sql并得到结果
int i = pstm.executeUpdate();
//5.处理结果
if(i>0){
System.out.println("增加成功");
}else{
System.out.println("增加失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//6.关闭连接
try {
DBUtil.get_CloseConn(null,pstm,conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
其他方法类似，不同的是查询中执行sql语句时要用executeQuery()而不是executeUpdate()
public void delUser(User user){
try {
//1.获取连接
conn = DBUtil.get_conn();
//2.获取存放sql语句对象
pstm = conn.prepareStatement("delete from tb_user where username = ?");
//3.填坑
System.out.println("待删除的用户名：");
user.setUsername(sc.nextLine());
pstm.setString(1,user.getUsername());
//4.执行sql语句
int i = pstm.executeUpdate();
//5.处理结果
if(i>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//6.关闭链接
try {
DBUtil.get_CloseConn(null,pstm,conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void modifyUser(User user){
try {
//1.获取连接
conn = DBUtil.get_conn();
//2.获取存放sql语句对象
pstm = conn.prepareStatement("update tb_user set username = ?,password = ? where id = ?");
//3.填坑
System.out.println("要修改的用户id:");
user.setId(sc.nextInt());
sc.nextLine();
System.out.println("用户的新用户名:");
user.setUsername(sc.nextLine());
System.out.println("用户的新密码:");
user.setPassword(sc.nextLine());
pstm.setString(1,user.getUsername());
pstm.setString(2,user.getPassword());
pstm.setInt(3,user.getId());
//4.执行sql语句
int i = pstm.executeUpdate();
//5.处理结果
if(i>0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//6.关闭链接
try {
DBUtil.get_CloseConn(null,pstm,conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void queryUser(User user){
try {
//1.获取连接
conn = DBUtil.get_conn();
//2.获取存放sql语句对象
pstm = conn.prepareStatement("select * from tb_user where username = ?");
//3.填坑
System.out.println("要查询的用户名:");
user.setUsername(sc.nextLine());
pstm.setString(1,user.getUsername());
//4.执行sql语句
rs = pstm.executeQuery();
//5.处理结果
int iCount = 0;
while(rs.next()) {
System.out.println("用户id:" + rs.getInt(1));
System.out.println("用户名:" + rs.getString(2));
System.out.println("用户密码:" + rs.getString(3) + '\n');
iCount++;
}
if(iCount == 0){
System.out.println("没有这个人");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//6.关闭链接
try {
DBUtil.get_CloseConn(rs,pstm,conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void menu(){
TestUser2 t2 = new TestUser2();
User user = new User();
Scanner sc1 = new Scanner(System.in);
for(;;){
System.out.println("\n\n");
System.out.println("1.增加用户");
System.out.println("2.删除用户");
System.out.println("3.修改用户信息");
System.out.println("4.查询用户信息");
System.out.println("5.退出");
System.out.println("选择：");
int ch = sc1.nextInt();
if (ch < 1 || ch > 5) {
System.out.println("错误输入");
}
else {
switch (ch){
case 1:
t2.addUser(user);
break;
case 2:
t2.delUser(user);
break;
case 3:
t2.modifyUser(user);
break;
case 4:
t2.queryUser(user);
break;
case 5:
System.exit(0);
}
}
}
}
试验一下
查看数据库中：
修改后：
删除结果：
299
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
