关于JDBC操作MySQL实例

此文只要介绍了JDBC连接MySQL数据库，以及对MySQL数据库中的表进行简单的增删改查操作

数据库中的表，位于testmysql数据库中，表名称user

用户信息类

public class User {
    private int id;
    private String userName;
    private String passWord;
    public User(){}
    public User(int id, String userName, String passWord) {
        super();
        this.id = id;
        this.userName = userName;
        this.passWord = passWord;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public String getPassWord() {
        return passWord;
    }
    public void setPassWord(String passWord) {
        this.passWord = passWord;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", userName=" + userName + ", passWord=" + passWord + "]";
    }


}

连接数据库工具类

import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DBUtil {
    //连接数据库的驱动
    private static String driver;
    //数据库的相关信息
    private static String url;
    //数据库用户名
    private static String username;
    //用户名密码
    private static String password;

    static{
        driver = "com.mysql.jdbc.Driver";
        url = "jdbc:mysql://localhost:3306/testmysql?useUnicode=true&characterEncoding=utf-8&useSSL=false";
        username = "root";
        password = "";
    }

    //连接数据库的方法
    public static Connection open(){
        try {
            //加载驱动
            Class.forName(driver);
            //获取连接
            return DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //断开连接的方法
    public static void close(Connection conn){
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //向表中添加的信息
    public static void add(){
        //获取数据库连接
        Connection conn = open();
        //所执行的sql语句
        String sql = "insert into user(userName,passWord) values(?,?)";
        try {
            //获得sql语句容器，预定义语句，用来动态操作数据库
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setString(1, "nini");
            psmt.setString(2, "123456");
            //获取操作受影响的行
            int i = psmt.executeUpdate();
            System.out.println(i);
            psmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //查询数据库方法
    public static void select(){
        User user = new User();
        String sql = "select * from user";
        Connection conn = open();
        try {
            Statement stmt = conn.createStatement();
            //ResultSet结果集元数据，如列名称，列类型等
            ResultSet rs = stmt.executeQuery(sql);
            //对结果集进行遍历
            while(rs.next()){
                user.setId(rs.getInt(1));
                user.setUserName(rs.getString(2));
                user.setPassWord(rs.getString(3));
                System.out.println(user);
            }
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //更新表数据的方法
    public static void update(){
        Connection conn = open();
        String sql = "update user set username=?,password=? where id =?";
        try {
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setString(1, "hahaha");
            psmt.setString(2,"hehehhe");
            psmt.setInt(3, 2);

            int i =psmt.executeUpdate();
            System.out.println(i);
            psmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //删除表中信息的方法
    public static void delete(){
        Connection conn = open();
        String sql = "delete from user where id=?";
        try {
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1, 2);
            int i = psmt.executeUpdate();
            System.out.println(i);
            psmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

测试类

import java.sql.SQLException;

public class Test {
    public static void main(String[] args) throws SQLException {
        //查询数据
        DBUtil2.select();
        //添加数据并显示
        DBUtil2.add();
        DBUtil2.select();
        //更新数据并显示
        DBUtil2.update();
        DBUtil2.select();
        //删除数据并显示
        DBUtil2.delete();
        DBUtil2.select();

    }
}