JDBC连接数据库crud

Myth丶恋晨 2022-05-15 03:50 173阅读 0赞

### 1、简单链接 ###

package com.kexin.database;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * 使用JDBC建立数据库连接
     * 
     * @author KeXin
     *
     */
    public class DataUtil {
        
        // ①加载驱动，放在static内防止多次加载占用内存
        static {
            try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                System.out.println("加载数据库驱动失败！");
                e.printStackTrace();
            }
        }
        static String url = "jdbc:mysql://localhost:3306/database1";
        static String user = "root";
        static String password = "root";
    
        // ②获取链接
        public static Connection getConnection() {
            Connection conn = null;
            try {
                conn = DriverManager.getConnection(url, user, password);
            } catch (SQLException e) {
                System.out.println("数据库连接失败！");
                e.printStackTrace();
            }
            return conn;
        }
    
        public static void main(String[] args) {
            // ③获取statement对象
            Connection conn = DataUtil.getConnection();
            Statement st = null;
            ResultSet result = null;
            try {
                st = conn.createStatement();
                // ④执行sql语句，获取结果集
                String sql = "select * from users";
                result = st.executeQuery(sql);
                String name, id, gender;
                int age;
                while (result.next()) {
                    name = result.getString("name");
                    id = result.getString("id");
                    gender = result.getString("gender");
                    age = result.getInt("age");
                    System.out.println(name + "\t" + id + "\t" + gender + "\t" + age);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                // ⑤关闭连接
                if (result != null) {
                    try {
                        result.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (st != null) {
                    try {
                        st.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
    
        }
    }

### 2、工具类连接 ###

package com.kexin.utils;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    /**
     * JDBC链接数据库工具类
     * ①获取链接
     * ②关闭连接
     * @author KeXin
     *
     */
    public class JdbcUtils {
        
            //使用配置文件存储数据库信息
            private static Properties prop = new Properties();
            // 加载驱动，放在static内防止多次加载占用内存
            static {
                try {
                    prop.load(JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"));
                    Class.forName(prop.getProperty("driver"));
                } catch (Exception e) {
                    throw new ExceptionInInitializerError(e);
                }
            }
            // 获取链接
            public static Connection getConnection() throws SQLException {
                return DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("user"), prop.getProperty("password"));
            }
            //关闭连接
            public static void release(Connection conn,Statement st,ResultSet result){
                if (result != null) {
                    try {
                        result.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (st != null) {
                    try {
                        st.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
    }

package com.kexin.database;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import com.kexin.utils.JdbcUtils;
    
    public class Demo {
        
        /**
         * 插入命令
         * @param sql
         * @throws SQLException
         */
        public void insert() throws SQLException {
            Connection conn = null;
            Statement st = null;
            ResultSet rs = null;
            try {
                conn = JdbcUtils.getConnection();
                st = conn.createStatement();
                String sql = "insert into users(id,name,gender,age) values('3','eee','女',22)";
                int r = st.executeUpdate(sql);
                if(r>0){
                    System.out.println("插入成功");
                }
            }finally {
                JdbcUtils.release(conn, st, rs);
            }
        }
    
        public void delete() throws SQLException {
            Connection conn = null;
            Statement st = null;
            ResultSet rs = null;
            try {
                conn = JdbcUtils.getConnection();
                st = conn.createStatement();
                String sql = "delete from users where id=4";
                int num = st.executeUpdate(sql);
                if(num>0){
                    System.out.println("删除成功！！");
                }
            }finally {
                JdbcUtils.release(conn, st, rs);
            }
        }
    
        public void find() throws SQLException {
            Connection conn = null;
            Statement st = null;
            ResultSet rs = null;
            try {
                conn = JdbcUtils.getConnection();
                st = conn.createStatement();
                String sql = "select id,name,gender,age from users where id=1";
                rs  = st.executeQuery(sql);
                String id,name,gender;
                int age;
                if(rs.next()){
                    id = rs.getString("id");
                    name = rs.getString("name");
                    gender = rs.getString("gender");
                    age = rs.getInt("age");
                    System.out.println(name + "\t"+id+"\t"+gender+"\t"+age);
                }
            }finally {
                JdbcUtils.release(conn, st, rs);
            }
        }
    
        public void update() throws SQLException {
            Connection conn = null;
            Statement st = null;
            ResultSet rs = null;
            try {
                conn = JdbcUtils.getConnection();
                st = conn.createStatement();
                String sql = "update users set name='fff' where id='4'";
                int num = st.executeUpdate(sql);
                if(num>0){
                    System.out.println("更新成功！！");
                }
            }finally {
                JdbcUtils.release(conn, st, rs);
            }
        }
    
        public void getAll() throws SQLException {
            Connection conn = null;
            Statement st = null;
            ResultSet rs = null;
            try {
    //          conn = JdbcUtils.getConnection();
    //          st = conn.createStatement();
    //          String sql = "select id,name,password,email,birthday from users";
    //          rs  = st.executeQuery(sql);
    //          List list = new ArrayList();
    //          while(rs.next()){
        
    //              User user = new User();
    //              user.setId(rs.getInt("id"));
    //              user.setName(rs.getString("name"));
    //              user.setPassword(rs.getString("password"));
    //              user.setEmail(rs.getString("email"));
    //              user.setBirthday(rs.getDate("birthday"));
    //              list.add(user);
    //          }
    //          
    //          System.out.println(list);
            }finally {
                JdbcUtils.release(conn, st, rs);
            }
        }
    }