JDBC连接数据库crud

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);
        }
    }
}