JDBC操作数据库

JDBC操作MYSQL数据库

首先解释下什么是JDSB，JDSB是标准的JAVA访问数据库的API，定义了数据库的连接、数据库语句的执行和查询结果集的遍历等。
JDBC的基本功能如下：

• 连接数据库
• 向数据库执行查询数据动作
• 向数据库执行更新数据动作
• 向数据库执行插入数据动作
• 向数据库执行删除数据动作
• 执行存储过程

实现JDBC连接MySQL数据库

提前配置好JAVA环境和MySQL，
准备IDEA（java最好的开发工具之一），Navicat（管理数据库）

1. 连接数据库

参考链接https://www.cnblogs.com/Ran-Chen/p/9646187.html
如在Test connection 遇到失败，可尝试在URL添加?serverTimezone=GMT或者?serverTimezone=UTC

连接数据库的代码为保持与后面的同步，可改为

package com.cn.jdbc;
import java.sql.*;
public class MySQLDemo{
    static String drivename = "com.mysql.cj.jdbc.Driver";
    static String url = "jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
    static String usename = "root";
    static String password = "********";//在这里填上登录数据库的密码
    static{
        try{
            Class.forName(drivename);
            System.out.println("创建驱动成功");
        }
        catch (ClassNotFoundException e){
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(url,usename,password);
            System.out.println("连接数据库成功");
        }catch (SQLException e){
            e.printStackTrace();
        }
        return conn;
    }
    public static void free(ResultSet rs, Connection conn, Statement stmt){
        try{
            if(rs != null)
                rs.close();
        }catch(SQLException e)
        {
            System.out.println("关闭ResultSet失败!");
            e.printStackTrace();
        }finally {
            try{
                if(conn != null)
                    conn.close();
            }catch (SQLException e){
                System.out.println("关闭Connectioin失败！");
                e.printStackTrace();
            }finally {
                try{
                    if(stmt != null)
                        stmt.close();
                }catch(SQLException e){
                    System.out.println("关闭Statement失败！");
                    e.printStackTrace();
                }
            }
        }
    }
    public static void main(String[] args)
    {
        MySQLDemo.getConnection();
    }
}

2. 使用JDCB向数据库表插入数据

表对应的实体类代码如下：

package com.cn.jdbc;
public class UserVo {
    private int id;
    private String name;
    private int age;
    private String tel;
    private String address;
    public String getAddress(){
        return address;
    }
    public void setAddress(String address){
        this.address = address;
    }
    public int getAge(){
        return age;
    }
    public void setAge(int age){
        this.age = age;
    }
    public int getId(){
        return id;
    }
    public void setId(int id){
        this.id=id;
    }
    public String getName(){
        return name;
    }
    public void setName(String name){
        this.name=name;
    }
    public String getTel(){
        return tel;
    }
    public void setTel(String tel){
        this.tel=tel;
    }
}

插入数据代码

package com.cn.jdbc;
import java.sql.*;
public class AddUser {
    public void add(com.cn.jdbc.UserVo userVo){
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try{
            conn = com.cn.jdbc.MySQLDemo.getConnection();
            String sql = "INSERT INTO users(id,name,age,tel,address)values(?,?,?,?,?)";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,userVo.getId());
            pstm.setString(2,userVo.getName());
            pstm.setInt(3,userVo.getAge());
            pstm.setString(4,userVo.getTel());
            pstm.setString(5,userVo.getAddress());
            pstm.executeUpdate();
            System.out.println("添加成功！添加的内容如下：");
            System.out.println("id:"+userVo.getId()+"\t name:"+userVo.getName()+"\t age:"+userVo.getAge()+"\t tel:"+userVo.getTel()+"\t address:"+userVo.getAddress());
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            com.cn.jdbc.MySQLDemo.free(rs,conn,pstm);
        }
    }
    public static void main(String[] args){
        AddUser addUser = new AddUser();
        com.cn.jdbc.UserVo userVo = new com.cn.jdbc.UserVo();
        int id = 207;
        String name = "赵六";
        int age=22;
        String tel="324242";
        String address = "北京海淀区";
        userVo.setId(id);
        userVo.setName(name);
        userVo.setAge(age);
        userVo.setAddress(address);
        addUser.add(userVo);
    }
}

3.使用JDBC查询数据库表数据

package com.cn.jdbc;
import java.sql.*;
import java.util.*;
public class Query{
    public List<com.cn.jdbc.UserVo> showUser(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<com.cn.jdbc.UserVo>list = new ArrayList<com.cn.jdbc.UserVo>();
        try{
            conn = com.cn.jdbc.MySQLDemo.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT * from users");
            while(rs.next()){
                com.cn.jdbc.UserVo userVo = new com.cn.jdbc.UserVo();
                userVo.setId(rs.getInt("id"));
                userVo.setName(rs.getString("name"));
                userVo.setAge(rs.getInt("age"));
                userVo.setTel(rs.getString("tel"));
                userVo.setAddress(rs.getString("address"));
                list.add(userVo);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            com.cn.jdbc.MySQLDemo.free(rs,conn,stmt);
        }
        return list;
    }
    public static void main(String[] args){
        Query query = new Query();
        List<com.cn.jdbc.UserVo>list = query.showUser();
        if(list!=null){
            System.out.print("id  ");
            System.out.print("name    ");
            System.out.print("age  ");
            System.out.print("tel        ");
            System.out.print("address            ");
            System.out.println();
        }
        for(int i = 0;i<list.size();i++)
        {
            System.out.print(list.get(i).getId()+"\t");
            System.out.print(list.get(i).getName()+"\t");
            System.out.print(list.get(i).getAge()+"\t");
            System.out.print(list.get(i).getTel()+"\t");
            System.out.print(list.get(i).getAddress()+"\t");
            System.out.println();
        }
    }
}

4.使用JDBC查询指定条件的数据

package com.cn.jdbc;
import java.sql.*;
import java.util.*;
public class QueryById{
    public com.cn.jdbc.UserVo queryUserById(int id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        com.cn.jdbc.UserVo userVo = null;
        try{
            conn = com.cn.jdbc.MySQLDemo.getConnection();
            pstmt=conn.prepareStatement("SELECT * from users where id = ?");
            pstmt.setInt(1,id);
            rs = pstmt.executeQuery();
            while(rs.next()){
                userVo = new com.cn.jdbc.UserVo();
                userVo.setId(rs.getInt("id"));
                userVo.setName(rs.getString("name"));
                userVo.setAge(rs.getInt("age"));
                userVo.setTel(rs.getString("tel"));
                userVo.setAddress(rs.getString("address"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            com.cn.jdbc.MySQLDemo.free(rs,conn,pstmt);
        }
        return userVo;
    }
    public static void main(String[] args){
        QueryById ById = new QueryById();
        int id = 207;
        com.cn.jdbc.UserVo vo = ById.queryUserById(id);
        if(vo != null){
            System.out.print("id\t");
            System.out.print("name\t");
            System.out.print("age\t");
            System.out.print("tel\t");
            System.out.print("  address");
            System.out.println();
            System.out.print(vo.getId()+"\t");
            System.out.print(vo.getName()+"\t");
            System.out.print(vo.getAge()+"\t");
            System.out.print(vo.getTel()+"\t");
            System.out.print(vo.getAddress()+"\t");
            System.out.println();
        }
    }
}

5.使用JDBC删除表数据

package com.cn.jdbc;

import java.sql.*;

public class DeleteUser {
    public void deleteUser(int id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try{
            conn = com.cn.jdbc.MySQLDemo.getConnection();
            String sql = "DELETE FROM users where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            pstmt.executeUpdate();
            System.out.println("删除成功！删除了id值为"+id+"的数据");
        }catch (SQLException  e){
            e.printStackTrace();
        }finally {
            com.cn.jdbc.MySQLDemo.free(null,conn,pstmt);
        }
    }
    public static void main(String[] args){
        DeleteUser deleteUser = new DeleteUser();
        int id = 1;
        com.cn.jdbc.UserVo userVo = new com.cn.jdbc.UserVo();
        com.cn.jdbc.QueryById queryById = new com.cn.jdbc.QueryById();
        userVo = queryById.queryUserById(id);
        if(userVo != null){
            deleteUser.deleteUser(id);
        }
        else{
            System.out.println("删除失败！原因：id为"+id+"的数据不存在！");
        }
    }

}

6.使用JDBC修改表数据

package com.cn.jdbc;
import java.sql.*;
public class UpdateUser{
    public void update(com.cn.jdbc.UserVo userVo){
        Connection conn = null;
        PreparedStatement pstmt = null;
        String sql = "Update users set id = ?,name=?,age=?,tel=?,address=?WHERE id = ?";
        try{
            conn = com.cn.jdbc.MySQLDemo.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,userVo.getId());
            pstmt.setString(2,userVo.getName());
            pstmt.setInt(3,userVo.getAge());
            pstmt.setString(4,userVo.getTel());
            pstmt.setString(5,userVo.getAddress());
            pstmt.setInt(6,userVo.getId());
            pstmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            com.cn.jdbc.MySQLDemo.free(null,conn,pstmt);
        }
    }
    public static void main(String[] args){
        UpdateUser updateUser = new UpdateUser();
        int id = 107;
        String name = "Jack";
        int age = 19;
        String tel = "123-465465";
        String address = "银河系";
        com.cn.jdbc.QueryById queryById = new com.cn.jdbc.QueryById();
        com.cn.jdbc.UserVo vo = new com.cn.jdbc.UserVo();
        vo=queryById.queryUserById(id);
        if(vo!=null){
            com.cn.jdbc.UserVo userVo = new com.cn.jdbc.UserVo();
            userVo.setId(id);
            userVo.setName(name);
            userVo.setAge(age);
            userVo.setTel(tel);
            userVo.setAddress(address);
            updateUser.update(userVo);
            System.out.println("修改成功！修改了id值为"+id+"的数据");
        }
        else {
            System.out.println("修改失败！原因：id为"+id+"的数据不存在");
        }
    }

}