java之JDBC

一.什么是JDBC

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

首先要获取连接：

 1 private static Connection getConn() {
 2     String driver = "com.mysql.jdbc.Driver";
 3     String url = "jdbc:mysql://localhost:3306/samp_db";
 4     String username = "root";
 5     String password = "";
 6     Connection conn = null;
 7     try {
 8         Class.forName(driver); //classLoader,加载对应驱动
 9         conn = (Connection) DriverManager.getConnection(url, username, password);
10     } catch (ClassNotFoundException e) {
11         e.printStackTrace();
12     } catch (SQLException e) {
13         e.printStackTrace();
14     }
15     return conn;
16 }

进行添加的代码例子：

 1 private static int insert(Student student) {
 2     Connection conn = getConn();
 3     int i = 0;
 4     String sql = "insert into students (Name,Sex,Age) values(?,?,?)";
 5     PreparedStatement pstmt;
 6     try {
 7         pstmt = (PreparedStatement) conn.prepareStatement(sql);
 8         pstmt.setString(1, student.getName());
 9         pstmt.setString(2, student.getSex());
10         pstmt.setString(3, student.getAge());
11         i = pstmt.executeUpdate();
12         pstmt.close();
13         conn.close();
14     } catch (SQLException e) {
15         e.printStackTrace();
16     }
17     return i;
18 }

进行更新的代码例子：

 1 private static int update(Student student) {
 2     Connection conn = getConn();
 3     int i = 0;
 4     String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'";
 5     PreparedStatement pstmt;
 6     try {
 7         pstmt = (PreparedStatement) conn.prepareStatement(sql);
 8         i = pstmt.executeUpdate();
 9         System.out.println("resutl: " + i);
10         pstmt.close();
11         conn.close();
12     } catch (SQLException e) {
13         e.printStackTrace();
14     }
15     return i;
16 }

进行查询的代码例子：

 1 private static Integer getAll() {
 2     Connection conn = getConn();
 3     String sql = "select * from students";
 4     PreparedStatement pstmt;
 5     try {
 6         pstmt = (PreparedStatement)conn.prepareStatement(sql);
 7         ResultSet rs = pstmt.executeQuery();
 8         int col = rs.getMetaData().getColumnCount();
 9         System.out.println("============================");
10         while (rs.next()) {
11             for (int i = 1; i <= col; i++) {
12                 System.out.print(rs.getString(i) + "	");
13                 if ((i == 2) && (rs.getString(i).length() < 8)) {
14                     System.out.print("	");
15                 }
16              }
17             System.out.println("");
18         }
19             System.out.println("============================");
20     } catch (SQLException e) {
21         e.printStackTrace();
22     }
23     return null;
24 }

进行删除的代码例子：

 1 private static int delete(String name) {
 2     Connection conn = getConn();
 3     int i = 0;
 4     String sql = "delete from students where Name='" + name + "'";
 5     PreparedStatement pstmt;
 6     try {
 7         pstmt = (PreparedStatement) conn.prepareStatement(sql);
 8         i = pstmt.executeUpdate();
 9         System.out.println("resutl: " + i);
10         pstmt.close();
11         conn.close();
12     } catch (SQLException e) {
13         e.printStackTrace();
14     }
15     return i;
16 }

jdbc在执行增删改查的时候的通用流程：

(1)创建Connection对象、SQL查询命令字符串；

　　(2)对Connection对象传入SQL查询命令，获得PreparedStatement对象；

　　(3)对PreparedStatement对象执行executeUpdate()或executeQurey()获得结果；

　　(4)先后关闭PreparedStatement对象和Connection对象。

　　可见，使用JDBC时，最常打交道的是Connection、PreparedStatement这两个类，以及select中的ResultSet类。