JDBC连接数据库

摘要

总体来说，jdbc连接数据库分为7步：

1、加载驱动
2、创建连接
3、创建PreparedStatement
4、执行PreparedStatement，获取ResultSet
5、关闭ResultSet
6、关闭Statement
7、关闭连接

---

步骤

1、加载驱动

提供mysql和oracle的驱动加载方式

Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动Class.forName("oracle.jdbc.driver.OracleDriver");// 动态加载oracle驱动

2、创建连接

String url = "jdbc:mysql://localhost:3306/dbname";String username = "root";String password = "pwd";Connection con = DriverManager.getConnection(url, username, password);

也可以调用getConnection(url)方法

url = "jdbc:mysql://localhost:3306/dbname?user=root&password=pwd&characterEncoding=UTF8";conn = DriverManager.getConnection(url);

3、创建PreparedStatement / Statement

ps=conn.prepareStatement("insert into User(name,age,email) values (?,?,?)");ps.setString(1, "by");ps.setString(2, "123");ps.setString(3, "[e-mail]");

4、执行PreparedStatement，获取ResultSet

row = ps.executeUpdate(); //执行update语句rs = ps.executeQuery(); //执行select语句

5、关闭ResultSet

rs.close();

6、关闭Statement

st.close();

7、关闭连接

conn.close();

代码实现

Connection conn = null;        PreparedStatement ps = null;        ResultSet rs = null;        // 出现乱码的情况可以在后面加上characterEncoding=utf-8        String url = "jdbc:mysql://localhost:3306/dbname";        String username = "root";        String password = "pwd";        try {            // 反射机制来动态加载mysql驱动            Class.forName("com.mysql.jdbc.Driver");            System.out.println("成功加载MySQL驱动程序");            // 一个Connection代表一个数据库连接            // 也可以 url =            // "jdbc:mysql://localhost:3306/dbname?user=root&password=pwd&characterEncoding=UTF8"            // 然后用方法 conn = DriverManager.getConnection(url); 获取连接            conn = DriverManager.getConnection(url, username, password);            // PreparedStatement相比较Statement来说可以防止SQL注入            // 方法executeUpdate可以实现插入，更新和删除等 ; executeQuery可以实现查询            // 查询数据            String selectSql = "select * from user where name = ?";            ps = conn.prepareStatement(selectSql);            ps.setString(1, "by");            rs = ps.executeQuery();            while (rs.next()) {                // 处理结果集                System.out.println("user[name] : " + rs.getString("name"));                System.out.println("user[age] : " + rs.getInt("age"));                System.out.println("user[email] : " + rs.getString("email"));            }            // 插入数据            String insertSql = "insert into user(name,age,email) values (?,?,?)";            ps = conn.prepareStatement(insertSql);            ps.setString(1, "by");            ps.setString(2, "123");            ps.setString(3, "[e-mail]");            int insertRow = ps.executeUpdate();            if (insertRow > 0) {                System.out.println("Insert Successful!");            }            // 删除数据            String delSql = "delete from user where name=?";            ps = conn.prepareStatement(delSql);            ps.setString(1, "by");            int delRow = ps.executeUpdate();            if (delRow > 0) {                System.out.println("Delete Successful!");            }        } catch (Exception e) {            System.out.println("MySQL操作错误");            e.printStackTrace();        } finally {            //关闭连接            if (rs != null) {                try {                    rs.close();                } catch (SQLException e) {                    e.printStackTrace();                }                rs = null;            }            if (conn != null) {                try {                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                }                conn = null;            }        }