【JDBC-Mysql】使用JDBC操作Mysql数据库

朴灿烈づ我的快乐病毒、 2023-10-08 20:35 65阅读 0赞

#### 【JDBC-Mysql】使用JDBC操作Mysql数据库 ####

*  1）导入依赖
 *  2）定义Connection连接类
 *  3）使用JDBC进行Mysql数据库操作
 *   *  3.1.写入
     *  3.2.删除
     *  3.3.修改
     *  3.4.查询

## 1）导入依赖 ##

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.7.27</version>
    </dependency>

## 2）定义Connection连接类 ##

public class Conn {
        
    	//定义Driver
        public static final String driver = "com.mysql.jdbc.Driver";
    	//定义getConnection方法获取Connection
        public static Connection getConnection(String url, String username, String password) {
        
            Connection conn = null;
            try {
        
            	//注册驱动
                Class.forName(driver);
                //获取Connection
                conn = DriverManager.getConnection(url, username, password);
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
            return conn;
        }
    	
    	//关闭连接
        public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        
            if (conn != null) {
        
                try {
        
                    conn.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if (ps != null) {
        
                try {
        
                    ps.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if (rs != null) {
        
                try {
        
                    rs.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
        }
    	
    	//关闭连接
        public static void close(Connection conn, PreparedStatement ps) {
        
            if (conn != null) {
        
                try {
        
                    conn.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if (ps != null) {
        
                try {
        
                    ps.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
    
        }
    }

## 3）使用JDBC进行Mysql数据库操作 ##

### 3.1.写入 ###

public class Test {
        
        private static final String tmpUrl = "jdbc:mysql://192.168.1.1:3306/test?characterEncoding=utf-8";
        private static final String tmpUsername = "root";
        private static final String tmpPassword = "123456";
        
        private static Connection conn;
        private static PreparedStatement ps;
        private static ResultSet rs;
    
        public static void main(String[] args) {
        
            conn = Conn.getConnection(tmpUrl, tmpUsername, tmpPassword);
            try {
        
            	//编辑sql，返回PreparedStatement状态
                ps = conn.prepareStatement("insert into `table` values(1234, '张三')");
    			//执行写入数据的操作
                rs = ps.executeUpdate();
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
            	//释放资源
                Conn.close(conn, ps, rs);
            }
        }
    }

### 3.2.删除 ###

public class Test {
        
        private static final String tmpUrl = "jdbc:mysql://192.168.1.1:3306/test?characterEncoding=utf-8";
        private static final String tmpUsername = "root";
        private static final String tmpPassword = "123456";
        
        private static Connection conn;
        private static PreparedStatement ps;
        private static ResultSet rs;
    
        public static void main(String[] args) {
        
            conn = Conn.getConnection(tmpUrl, tmpUsername, tmpPassword);
            try {
        
            	//编辑sql，返回PreparedStatement状态
                ps = conn.prepareStatement("delete from `table` where id = ?");
                //定义参数（?按照顺序进行参数配置，如下是删除id为1234的数据）
                ps.setInt(1,1234)
                //执行删除数据的操作
                rs = ps.executeUpdate();
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
            	//释放资源
                Conn.close(conn, ps, rs);
            }
        }
    }

### 3.3.修改 ###

public class Test {
        
        private static final String tmpUrl = "jdbc:mysql://192.168.1.1:3306/test?characterEncoding=utf-8";
        private static final String tmpUsername = "root";
        private static final String tmpPassword = "123456";
        
        private static Connection conn;
        private static PreparedStatement ps;
        private static ResultSet rs;
    
        public static void main(String[] args) {
        
            conn = Conn.getConnection(tmpUrl, tmpUsername, tmpPassword);
            try {
        
            	//编辑sql，返回PreparedStatement状态
                ps = conn.prepareStatement("update `table` set name = '张三' where id = ?");
                //定义参数（?按照顺序进行参数配置，如下是修改id为1234的数据）
                ps.setInt(1,1234)
                //执行修改数据的操作
                rs = ps.executeUpdate();
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
            	//释放资源
                Conn.close(conn, ps, rs);
            }
        }
    }

### 3.4.查询 ###

public class Test {
        
        private static final String tmpUrl = "jdbc:mysql://192.168.1.1:3306/test?characterEncoding=utf-8";
        private static final String tmpUsername = "root";
        private static final String tmpPassword = "123456";
        
        private static Connection conn;
        private static PreparedStatement ps;
        private static ResultSet rs;
    
        public static void main(String[] args) {
        
            conn = Conn.getConnection(tmpUrl, tmpUsername, tmpPassword);
            try {
        
            	//编辑sql，返回PreparedStatement状态
                ps = conn.prepareStatement("SELECT `id`, `name` FROM `table`");
                //返回ResultSet结果集
                rs = ps.executeQuery();
                //遍历结果集
                while (rs.next()) {
        
                	//获取每条数据的id，name（两种写法都可以）
                	//int id = rs.getInt("id");
                	int id = rs.getInt(1);
                	//String name = rs.getString("name");
                	String name = rs.getString(2);
                }
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
            	//释放资源
                Conn.close(conn, ps, rs);
            }
        }
    }