JDBC之CRUD

JDBC定义

　Java 数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。它JDBC是面向关系型数据库的。

准备工作

1. 创建mysql数据库表信息（student表）
2. 导入相应的jar
3. 编写javabean类

导入jar包：

import com.mysql.jdbc.Connection;import com.mysql.jdbc.PreparedStatement;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;

创建javabean类
　

public class Student {        private String Id;        private String Name;        private String Sex;        private String Age;        Student(String Name, String Sex, String Age) {            this.Id = null; //default            this.Name = Name;            this.Sex = Sex;            this.Age = Age;        }        public String getId() {            return Id;        }        public void setId(String Id) {            this.Id = Id;        }        public String getName() {            return Name;        }        public void setName(String Name) {            this.Name = Name;        }        public String getSex() {            return Sex;        }        public void setSex(String Sex) {            this.Sex = Sex;        }        public String getAge() {            return Age;        }        public void setage(String Age) {            this.Age = Age;        }}

JDBC获取连接

private static Connection getConn() {    String driver = "com.mysql.jdbc.Driver";    String url = "jdbc:mysql://localhost:3306/student";    String username = "root";    String password = "123456";    Connection conn = null;    try {        Class.forName(driver); //classLoader,加载对应驱动        conn = (Connection) DriverManager.getConnection(url, username, password);    } catch (ClassNotFoundException e) {        e.printStackTrace();    } catch (SQLException e) {        e.printStackTrace();    }    return conn;}

CRUD 操作（database：student table：students）

//增private static int insert(Student student) {    Connection conn = getConn();    int i = 0;    String sql = "insert into students (Name,Sex,Age) values(?,?,?)";    PreparedStatement pstmt;    try {        pstmt = (PreparedStatement) conn.prepareStatement(sql);        pstmt.setString(1, student.getName());        pstmt.setString(2, student.getSex());        pstmt.setString(3, student.getAge());        i = pstmt.executeUpdate();        pstmt.close();        conn.close();    } catch (SQLException e) {        e.printStackTrace();    }    return i;}----------//删private static int delete(String name) {    Connection conn = getConn();    int i = 0;    String sql = "delete from students where Name='" + name + "'";    PreparedStatement pstmt;    try {        pstmt = (PreparedStatement) conn.prepareStatement(sql);        i = pstmt.executeUpdate();        System.out.println("resutl: " + i);        pstmt.close();        conn.close();    } catch (SQLException e) {        e.printStackTrace();    }    return i;}----------//改private static int update(Student student) {    Connection conn = getConn();    int i = 0;    String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'";    PreparedStatement pstmt;    try {        pstmt = (PreparedStatement) conn.prepareStatement(sql);        i = pstmt.executeUpdate();        System.out.println("resutl: " + i);        pstmt.close();        conn.close();    } catch (SQLException e) {        e.printStackTrace();    }    return i;}----------//查private static Integer getAll() {    Connection conn = getConn();    String sql = "select * from students";    PreparedStatement pstmt;    try {        pstmt = (PreparedStatement)conn.prepareStatement(sql);        ResultSet rs = pstmt.executeQuery();        int col = rs.getMetaData().getColumnCount();        System.out.println("============================");        while (rs.next()) {            for (int i = 1; i <= col; i++) {                System.out.print(rs.getString(i) + "\t");                if ((i == 2) && (rs.getString(i).length() < 8)) {                    System.out.print("\t");                }             }            System.out.println("");        }    } catch (SQLException e) {        e.printStackTrace();    }    return null;}

测试

1. 打开mysql服务 windows命令： net start mysql
2. 测试代码：

public static void main(String args[]) {    JDBCOperation.getAll();    JDBCOperation.insert(new Student("Toom", "Male", "14"));    JDBCOperation.getAll();    JDBCOperation.update(new Student("Bean", "Female", "7"));    JDBCOperation.delete("Toom");    JDBCOperation.getAll();}

JDBC总结

• 第一步:注册驱动
方式一：DriverManager.registerDriver(new Driver()); //通过查看源码,底层还会再次创建Driver,不推荐。
注意： 这里的new Driver()指的是—>实现类”com.mysql.jdbc.Driver()”-实现了接口 ”java.sql.Driver”
方式二： Class.forName(“驱动路径”); // com.mysql.jdbc.Driver ,开发中推荐

• 第二步:获取连接对象Connection
作用:连接数据库的对象,获取执行SQL语句的Statement对象
Connection conn = DriverManager.getConnection();

• 第三步:获取执行SQL语句的Statement

Statement stmt = conn.createStatement(); // 获取Statement对象, 存在SQL注入问题,不推荐
PreparedStatement ps = conn.prepareStatement(); //预编译对象

• 第四步:执行SQL语句

 // 执行增删改查都通用 boolean b = ps.execute(); //如果第一个结果是 ResultSet 对象，则返回 true；如果第一个结果是更新计数或者没有结果，则返回 false  //增删改的操作 int num = ps.executeUpdate(); //查询操作 ResultSet rs = ps.executeQuery(); //遍历结果集 while(rs.next()) { //获取记录 getInt(列的值); //int i = rs.getInt(1); getInt(列的名称); // rs.getInt("userid") getString(列的值); // String username = rs.getString(2); getString(列的名称); //String username= rs.getString("username");

• 第五步: 释放资源