# JDBC -- The Java™ Tutorials 

# Study Note of JDBC

JDBC Study Note ----connect to database

通常，使用JDBC执行SQL语句需要下面5 个步骤:

1> 建立一个连接         | establish a connection

2> 构造一条语句         | create a statement

3> 执行语句             | execute the query

4> 处理结果             | process the resultset object

5> 关闭连接             | close the connection

 2. 构造一条语句

    语句的三种类型及创建方式 :

    1> Statement:

    # 用于实现简单的SQL语句(不带参数)

    Statememt stmt = con.createStatement();

    2> PrepareStatement(EXtends Statement):

    # 预编译带参数的SQL语句(多次使用的SQL语句)

    PreparedStatement psmt = con.prepareStatement(String sql);

    3> CallableStatement(Extends PrepareStatement):

    # 用于执行存储过程(可能同时含有传入和传出参数)    

    CallableStatement csmt = con.prepareCall(String sql);

2.1 statement 的创建：

public Statement getstatement(Connection con) throws SQLException{          Statement stmt = null;          stmt = con.createStatement();          System.out.println("get Statement successfully!");        return stmt;      }

2.2 Prepared Statements

    PreparedStatement 比 Statement 的执行时间更快.因为PreparedStatement 会预先编译SQL语句,然后再发给DBMS 执行,这会比Statement 省去了编译的时间。即便不带参数的SQL语句可以使用PreparedStatement，但是通常情况下PrepareStatement 常用于处理带参数的SQL。

public class sqlparam {    private int type;    private Object value;    public int getType() {        return type;    }    public void setType(int type) {        this.type = type;    }    public Object getValue() {        return value;    }    public void setValue(Object value) {        this.value = value;    }}

import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.Date;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;import java.util.HashMap;import java.util.InvalidPropertiesFormatException;import java.util.List;import java.util.Map;import java.util.Properties;public class myjdbcutil {      public String dbms;      public String dbName;       public String userName;      public String password;            private String serverName;      private int portNumber;      private Properties prop;            public myjdbcutil(String filename) throws FileNotFoundException, InvalidPropertiesFormatException, IOException{          this.setProperties(filename);      }            /* get properties from .properties file       * and set these values to Corresponding fields        * */      private void setProperties(String fileName) throws FileNotFoundException,IOException,InvalidPropertiesFormatException {            this.prop = new Properties();            InputStream fis =  this.getClass().getResourceAsStream(fileName);            prop.load(fis);                        this.dbms = this.prop.getProperty("dbms");            this.dbName = this.prop.getProperty("database_name");            this.userName = this.prop.getProperty("user_name");            this.password = this.prop.getProperty("password");            this.serverName = this.prop.getProperty("server_name");            this.portNumber = Integer.parseInt(this.prop.getProperty("port_number"));                        System.out.println("Set the following properties:");            System.out.println("dbms: " + dbms);            System.out.println("dbName: " + dbName);            System.out.println("userName: " + userName);            System.out.println("password: " + password);            System.out.println("serverName: " + serverName);            System.out.println("portNumber: " + portNumber);      }            /* step 1 to execute query using JDBC       * get connection with connection properties       * */      public Connection getConnection() throws ClassNotFoundException, SQLException{          Class.forName("oracle.jdbc.driver.OracleDriver");          System.out.println("Oracle JDBC Driver Registered!");                    Connection conn = null;          Properties connectionProps = new Properties();          connectionProps.put("user", this.userName);          connectionProps.put("password", this.password);          if (this.dbms.equals("oracle")) {              String dburl = "jdbc:"+this.dbms+":"+dbName+":@"+this.serverName+":"+this.portNumber+"/xe";              System.out.println("dburl is:"+dburl);              conn = DriverManager.getConnection(dburl, connectionProps);              System.out.println("Connected to database successfully!");          }          return conn;    }      public Statement getstatement(Connection con) throws SQLException{          Statement stmt = null;          stmt = con.createStatement();          System.out.println("get Statement successfully!");        return stmt;      }      public PreparedStatement getpreparedstatement(Connection con,String sql) throws SQLException{          PreparedStatement psmt = con.prepareStatement(sql);          return psmt;      }            public PreparedStatement getpreparedstatement(Connection con,String sql, List
     
       params ) throws SQLException{          PreparedStatement psmt = con.prepareStatement(sql);          int counter = 0;          for(sqlparam parm : params){              switch (parm.getType()){              case java.sql.Types.VARCHAR:                  psmt.setString(counter, (String)parm.getValue());                  break;              case java.sql.Types.INTEGER:                  psmt.setInt(counter, (Integer)parm.getValue());                  break;              case java.sql.Types.NUMERIC:                  psmt.setLong(counter, (Long)parm.getValue());                  break;              case java.sql.Types.BOOLEAN:                  psmt.setBoolean(counter, (Boolean)parm.getValue());                  break;              case java.sql.Types.FLOAT:                  psmt.setFloat(counter, (Float)parm.getValue());                  break;              case java.sql.Types.DOUBLE:                  psmt.setDouble(counter, (Double)parm.getValue());                  break;              case java.sql.Types.DATE:                  psmt.setDate(counter, (Date)parm.getValue());                  break;              case java.sql.Types.TIMESTAMP:                  psmt.setTimestamp(counter, (java.sql.Timestamp)parm.getValue());                  break;              case java.sql.Types.NULL:                  psmt.setNull(counter, getsqlType(parm.getValue()));                  break;              default:                  psmt.setObject(counter, parm.getValue());                  break;              }              counter++;          }          return psmt;      }    private int getsqlType(Object value) {        String classname = value.getClass().getName();        if (classname.equals("java.lang.String")){            return java.sql.Types.VARCHAR;        }        else if(classname.equals("java.lang.Integer")){            return java.sql.Types.INTEGER;        }        else if(classname.equals("java.lang.Long")){            return java.sql.Types.NUMERIC;        }        else if(classname.equals("java.lang.Float")){            return java.sql.Types.FLOAT;        }        else if(classname.equals("java.lang.Double")){            return java.sql.Types.DOUBLE;        }        else if(classname.equals("java.lang.Boolean")){            return java.sql.Types.BOOLEAN;        }        else if(classname.equals("java.sql.Date")){            return java.sql.Types.DATE;        }        else if(classname.equals("java.sql.Timestamp")){            return java.sql.Types.TIMESTAMP;        }        else{            return java.sql.Types.OTHER;        }    }    public Map
      
        extractresultset(ResultSet rs,int rownum) throws SQLException {        Map
       
         selects = new HashMap
        
         ();        ResultSetMetaData metadata = rs.getMetaData();        for (int p = 1 ; p <= metadata.getColumnCount(); ++p) {            String colName = metadata.getColumnName(p);            switch (metadata.getColumnType(p)) {                case java.sql.Types.NUMERIC:                case java.sql.Types.INTEGER:                {                    selects.put(colName, new Long(rs.getLong(p)));                    break;                }                case java.sql.Types.CHAR:                case java.sql.Types.VARCHAR:                {                    selects.put(colName, rs.getString(p));                    break;                }                case java.sql.Types.BLOB:                {                    selects.put(colName, rs.getBlob(p));                    break;                }                case java.sql.Types.BOOLEAN:                {                    selects.put(colName, new Boolean(rs.getBoolean(p)));                    break;                }                case java.sql.Types.DATE:                {                    selects.put(colName, rs.getDate(p));                    break;                }                case java.sql.Types.DECIMAL:                case java.sql.Types.DOUBLE:                case java.sql.Types.FLOAT:                case java.sql.Types.REAL:                {                    selects.put(colName, new Double(rs.getDouble(p)));                    break;                }                case java.sql.Types.TIME:                {                    selects.put(colName, rs.getTime(p));                    break;                }                case java.sql.Types.TIMESTAMP:                {                    selects.put(colName, rs.getTimestamp(p));                    break;                }                case java.sql.Types.JAVA_OBJECT:                {                    selects.put(colName, rs.getObject(p));                    break;                }                default:                {                    selects.put(colName, rs.getObject(p));                    break;                }            }        }        return selects;    }}
        
       
      
     

package myjdbc;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import java.util.Map.Entry;/* execute in oracle DB before test *  * create table SUPPLIES( * SUP_ID integer NOT NULL, * SUP_NAME varchar(40) NOT NULL, * STREET varchar(40) NOT NULL, * CITY varchar(20) NOT NULL, * STATE varchar(2) NOT NULL, * ZIP varchar(5) * ); *  * insert into SUPPLIES values(49,  'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', '95460'); * insert into SUPPLIES values(101, 'Acme, Inc.', '99 Market Street', 'Groundsville', 'CA', '95199'); * insert into SUPPLIES values(150, 'The High Ground', '100 Coffee Lane', 'Meadows', 'CA', '93966'); * insert into SUPPLIES values(456, 'Restaurant Supplies, Inc.', '200 Magnolia Street', 'Meadows', 'CA', '93966'); * insert into SUPPLIES values(927, 'Professional Kitchen', '300 Daisy Avenue', 'Groundsville', 'CA', '95199'); */public class TestJdbc {        public static void main(String[] args) {        // TODO Auto-generated method stub        try{            myjdbcutil db = new myjdbcutil("mydb.properties");            Connection con = db.getConnection();            Statement stmt = db.getstatement(con);            String SqlDrop = "drop table SUPPLIES";            String SqlCreate = "create table SUPPLIES("                        +" SUP_ID integer NOT NULL,"                        +" SUP_NAME varchar(40) NOT NULL,"                        +" STREET varchar(40) NOT NULL,"                        +" CITY varchar(20) NOT NULL,"                        +" STATE varchar(2) NOT NULL,"                        +" ZIP varchar(5))";            String SqlInsert = "insert into SUPPLIES values(49,  'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', '95460')";            String SqlSelect = "select * from SUPPLIES";            stmt.execute(SqlDrop);            stmt.execute(SqlCreate);            stmt.execute(SqlInsert);            List

 

2.3 Callable Statements