JDBC浅析

永久更新链接

• 个人博客

JDBC介绍

• 百度百科关于JDBC的介绍

JDBC常用接口和类

interface Driver

• The interface that every driver class must implement.
• 这是每个驱动类都必须实现的接口
• 以SQLlite为例,public class org.sqlite.JDBC implements java.sql.Driver实现该接口
• Class.forName("org.sqlite.JDBC")
  is equivalent to:
  Class.forName("org.sqlite.JDBC", true, this.getClass().getClassLoader())

• Class.forName(name,initialize,loader)
  name - fully qualified name of the desired class
  initialize - if true the class will be initialized.
  loader - class loader from which the class must be loaded

• 例:

  Class.forName("org.sqlite.JDBC");

interface Connection

• A connection (session) with a specific database.
• Statement createStatement() throws SQLExceptionCreates a Statement object for sending SQL statements to the database. SQL statements without parameters are normally executed using Statement objects. If the same SQL statement is executed many times, it may be more efficient to use a PreparedStatement object.
• PreparedStatement prepareStatement(String sql)throws SQLExceptionCreates a PreparedStatement object for sending parameterized SQL statements to the database

• 注意:无参数的SQL使用createStatement()方法创建Statement对象;有参数的使用prepareStatement()创建PreparedStatement对象,防止SQL注入

• 例:
  Connection c = null;

Class DriverManager

• 该类重载3个getConnection方法得到Connection

  1. getConnection(String url)
     SQLite使用此方法,传入数据库文件的地址,返回Connection
  2. getConnection(String url, Properties info)
     info为参数列表,正常情况下至少包含帐号密码
  3. getConnection(String url, String user, String password)
     MySQL连接时可在url中指明字符集useUnicode=true&characterEncoding=utf-8&useSSL=false

• 例:
  c = DriverManager.getConnection("jdbc:sqlite:" + file.db);
  注:file.db为SQLite数据库文件路径

interface Statement

• The object used for executing a static SQL statement and returning the results it produces.
• ResultSet executeQuery(String sql)throws SQLException 使用静态SQL语句返回ResultSet对象
• int executeUpdate(String sql)throws SQLException INSERT, UPDATE, or DELETE等不返回ResultSet的SQL语句使用此方法,返回值为DML的行数(不懂)或者返回0
• 注意:传入的SQL语句直接传给数据库,如果组成sql的字符串有输入的部分,存在SQL注入的危险,建议使用PreparedStatement
• 例:
  stmt = c.createStatement();
String sql = "CREATE TABLE USER(ID INT PRIMARY KEY NOT NULL,USERNAME TEXT NOT NULL ,PASSWORD INT NOT NULL)";
stmt.executeUpdate(sql);

interface PreparedStatement extends tatement

• An object that represents a precompiled SQL statement.
• 继承自Statement,预编译SQL语句的部分,传入SQL语句中的参数,因为SQL语句部分已经编译,可以解决由Statement带来的SQL注入风险,
  因为SQL语句部分已经被编译,所以有更高的执行效率,多次提交的SQL语句建议用此类的对象
• 例:
  PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00)
pstmt.setInt(2, 110592)
  使用setXXX方法设置参数,如上所示
  常用set方法
  setString(int parameterIndex, String x)
setTime(int parameterIndex, Time x)
setInt(int parameterIndex, int x)

interface ResultSet

• A table of data representing a database result set, which is usually generated by executing a statement that queries the database.
• boolean next()Moves the cursor forward one row from its current position.
• getString(String columnLabel)以字符串形式返回游标(cursor)所在行,属性为columnLable的数据元素的值