JDBC学习笔记

JDBC学习手记

注：
1、JDBC很多数据的索引值（下标）都是从1开始，而不是数据那样的从0开始。

*******************************************************************************************************************
15:20 2012/3/16
JDBC小程序：
public static void main(String[] args) throws SQLException {
        //注册sql server驱动
        DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
        String url = "jdbc:sqlserver://localhost:2644;DatabaseName=scl3";
        String user = "sa";
        String password = "123456";
        //链接数据库
        Connection conn = DriverManager.getConnection(url, user, password);
        //创建语句
        Statement statement = conn.createStatement();
        String sqlstr = "SELECT * FROM dbo.a_article WHERE id='15891'";
        //执行语句
        ResultSet rs = statement.executeQuery(sqlstr);
        //处理结果
        if(rs.next()){
            System.out.println(rs.getObject(1));
        }
        rs.close();
        statement.close();
        conn.close();
    }
*******************************************************************************************************************

15:26 2012/3/16
JDBC详解：
1、注册驱动
DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
System.setProperty("jdbc:drivers", "com.microsoft.sqlserver.jdbc.SQLServerDriver");
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
2、连接数据库
URL格式：JDBC:子协议:子名称//主机名称:端口/数据库名?属性名=属性值&……
String url = "jdbc:sqlserver://localhost:2644;DatabaseName=scl3";
3、创建语句
把sql语句发给数据库并把数据库查询结果返回的作用。
4、处理数据
5、关闭连接
数据库的连接是soket连接，所以处理完数据后要释放资源。
*******************************************************************************************************************

16:30 2012/3/16
数据库增删改查
1、查
ResultSet rs = statement.executeQuery(sqlstr);
2、增
int i =statement.executeUpdate(sqlstr);返回影响行数
3、改
int i =statement.executeUpdate(sqlstr);返回影响行数
4、删
int i =statement.executeUpdate(sqlstr);返回影响行数
*******************************************************************************************************************

17:25 2012/3/16
防参数注入
String sqlstr = "SELECT * FROM dbo.a_article WHERE id=?";
PreparedStatement ps = conn.prepareStatement(sqlstr);
ps.setString(1, "15891");
ResultSet rs = ps.executeQuery();
1、作用：可以防网站输入参数注入式攻击
2、代码执行效率高，耗时低
3、耗时高的是在与数据库连接时耗时高。
4、Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出
*******************************************************************************************************************

1:37 2012/3/17
java.sql.Date是继承java.util.Date的子类，只显示日期不显示时间，故在把java.util.Date存入数据库时，需要用new java.sql.Date()的方式把其转换为java.sql.Date类型。
在输出时不需要转换
*******************************************************************************************************************

1:40 2012/3/17
大段文本数据类型
*******************************************************************************************************************

0:10 2012/3/18
事物(ACID)
1、原子性(atomicity)：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。
2、一致性(consistency)：在事务处理执行前后，数据库是一致的（数据库数据完整性约束）。
3、隔离性(isolcation)：一个事务处理对另一个事务处理的影响。
4、持续性(durability)：事物处理的效果能够被永久保存下来。
connection.setAutoCommit(false);//打开事物。
connection.commit();//提交事物。
connection.rollback();//回滚事物。
example：
//创建事物
cnnection.setAutoCommit(false);
sql处理语句处理……
//提交事物
connection.commit();
//出异常时回滚事物
connection.rollback();
*******************************************************************************************************************

1:04 2012/3/18
事物的保存点处理
当事物出错了，但是我们不需要把所有程序都回滚，这时在程序中设置回滚点，让程序回滚到该点时停止回滚。
//初始化回滚点
Savepoint sp = conn.setSavepoint();
//当捕获异常时，让程序只回滚到设定回滚点的地方。只需要把回滚点对象传给回滚方法即可。
conn.rollback(sp);
//回滚之后，提交需要提交的数据。
conn.commit();
*******************************************************************************************************************

1:17 2012/3/18
事物(JTA)分布
1、跨越多个数据源的事物，使用JTA容器实现事物。
2、分成两阶段提交。
javax.transaction.UserTransaction tx = (UserTransaction)ctx.lookup("jndiName");
tx.begin();
//connection1 connection2(可能来自不同的数据库)、、、
tx.commit();//tx.rollback();
注：JTA容器是要服务器支持，tomcat不支持JTA，只要Weblogo或者Jboss等高级服务器才能支持。
*******************************************************************************************************************

1:30 2012/3/18
事物的隔离级别
隔离级别分多线程并发读取数据时的正确性。
1、connection.setTransactionlsolation(Connection.TRANSACTION_READ_COMMITTED)；
2、V:可能出现，X：不会出现
------------------------------------------------------------------------------
|隔离级别                                        |脏读    |不可重复读 |幻读  |
|------------------------------------------|-------|------------------|-------|
|读未提交(Read uncommitted)       |V       |V                     |V       |
|------------------------------------------|-------|------------------|-------|
|读已提交(Read committed)           |X       |V                      |V       |
|------------------------------------------|-------|------------------|-------|
|可重复读（Repeatable read）      |X      |X                      |V       |
|------------------------------------------|-------|------------------|-------|
|可串行读(Serializable)                    |X       |X                      |X       |
------------------------------------------------------------------------------
*******************************************************************************************************************

2:33 2012/3/18
JDBC调用存储过程
//声明一个存储过程执行对象
CallableStatement cs = null;(从PreperedStatement扩展来)
//设置存储过程名称与参数数量
cs = connection.prepareCall("{call psname(?,?,?)}");
//注册一个返回参数及类型
cs.registerOutParameter(index,Types.INTEGER);
//设置参数及参数值
cs.setXXX(i,xxxx);
//执行存储过程
cs.excuteUpdate();
//获得返回值
int id = cs.getInt(index);
*******************************************************************************************************************

3:09 2012/3/18
JDBC操作数据库其他API
1、产生主键
connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS)；
PreparedStatement.getGeneratedKeys();
//PreparedStatement.getGeneratedKeys(sql,Statement.RETURN_GENERATED_KEYS);
ps.excuteUpdate();
ResultSet rs = st.getGeneratedKeys();
rs.getInt(1);
2、批处理
批处理可以大幅度提升大量增、删、改的速度。
PreparedStatement.addBatch();
PreparedStatement.excuteBatch();
example:
--------------------------------------------------------------------------
    public static void JDBCBatch() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Savepoint sp = null;
        String url = "jdbc:sqlserver://localhost:2644;DatabaseName=JDBCDemo";
        String user = "sa";
        String password = "123456";
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(url, user, password);
            String sql1 = "INSERT dbo.users( username, money ) VALUES  (?,?)";
            
            ps = conn.prepareStatement(sql1);
            for(int i =0;i<100;i++){
                ps.setString(1, "flame"+i);
                ps.setInt(2, i);
                ps.addBatch();
            }
            int[] arr = ps.executeBatch();
            System.out.println(arr);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if (ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

        }
    }
--------------------------------------------------------------------------
*******************************************************************************************************************

10:59 2012/3/18
JDBC事物处理举例
public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Savepoint sp = null;
        String url = "jdbc:sqlserver://localhost:2644;DatabaseName=JDBCDemo";
        String user = "sa";
        String password = "123456";
        String sql = "SELECT id,username,money FROM dbo.users";
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(url, user, password);
            //打开事物处理
            conn.setAutoCommit(false);
            /**
             * 把张三的账号中的money减去30块
             * 先从李四的账号拿出100块加入到王五的账号中，如果王五账号超过200时不执行此操作。
             * UPDATE dbo.users SET money = money-30 WHERE username = 'zhangsan'
             * UPDATE dbo.users SET money = money-100 WHERE username = 'lisi'
             * UPDATE dbo.users SET money = money+100 WHERE username = 'wangwu'
             */
            String sql1 = "UPDATE dbo.users SET money = money-30 WHERE username = 'zhangsan'";
            ps = conn.prepareStatement(sql1);
            ps.executeUpdate();
            sp = conn.setSavepoint();
            
            String sql2 = "UPDATE dbo.users SET money = money-100 WHERE username = 'lisi'";
            ps = conn.prepareStatement(sql2);
            ps.executeUpdate();
            
            String sql3 = "SELECT money FROM dbo.users WHERE username = 'wangwu'";
            ps = conn.prepareStatement(sql3);
            rs = ps.executeQuery();
            if(rs.next()){
                if(rs.getInt("money")>200){
                    throw new MoneyOutException("金额超出！");
                }
            }
            sql3 = "UPDATE dbo.users SET money = money+100 WHERE username = 'wangwu'";
            ps = conn.prepareStatement(sql3);
            ps.executeUpdate();
            //提交
            conn.commit();
        }catch (MoneyOutException e) {
            if(conn != null && sp!= null){
                try {
                    //回滚到保存点
                    conn.rollback(sp);
                    //提交
                    conn.commit();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            if(rs != null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            
        }
    }

*******************************************************************************************************************

13:58 2012/3/18
1、可滚动的结果集
Statement st = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery(sql);
rs.beforeFirst();
rs.afterLast();
rs.isFirst();
rs.last();
rs.isLast();
rs.absolute(9);
rs.moveToInsertRow();
2、可更新的结果集
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,RestltSet.CONCUR_UPDATABLE);
rs.updateString("col name","new value");
rs.updateRow();
*******************************************************************************************************************

14:54 2012/3/18
数据库的元数据信息
1、DatabaseMetaData meta = connection.getMetaData();
2、通过DatabaseMetaData可以获得数据库相关的信息。如：数据库版本、数据库名、数据库厂商信息、是否支持事务、是否支持某种事务隔离级别、是否支持滚动结果集。
3、ParameterMetaData pmd = preparedStatement.getParameterMetaData();
4、通过ParameterMetaData可以获得参数信息。
5、ResultSetMetaData rsmd = rs.getMetaData();
6、通过ResultSetMetaData可以获得返回结果集的信息。
*******************************************************************************************************************

15:11 2012/3/18
获得数据库元数据信息example：
-------------------------------------------------
    public static void main(String[] args) throws Exception {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        String url = "jdbc:sqlserver://localhost:2644;DatabaseName=scl3";
        String user = "sa";
        String password = "123456";
        //链接数据库
        Connection conn = DriverManager.getConnection(url, user, password);
        DatabaseMetaData metaData = conn.getMetaData();
        System.out.println("metaData.getDatabaseMajorVersion():"+metaData.getDatabaseMajorVersion());
        System.out.println("metaData.getDatabaseProductName():"+metaData.getDatabaseProductName());
        System.out.println("metaData.getDatabaseProductVersion():"+metaData.getDatabaseProductVersion());
        System.out.println("metaData.supportsTransactions():"+metaData.supportsTransactions());
        conn.close();
    }
-------------------------------------------------
*******************************************************************************************************************

15:36 2012/3/18
获取结果集元数据信息，并把结果集封装到Map中，最后将所有数据封装在arraylist集合里面：
1、map的key是列名
2、map的值是列的value
3、将map封装在arraylist集合（显示整张表）
example：
————————————————————————————————
    public static void main(String[] args) throws SQLException, Exception {
        List<Map<String, Object>> list = getResult("SELECT * FROM users WHERE id <20");
        System.out.println(list);
    }
    public static List<Map<String, Object>> getResult(String sql)
            throws ClassNotFoundException, SQLException {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        String url = "jdbc:sqlserver://localhost:2644;DatabaseName=JDBCDemo";
        String user = "sa";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, user, password);
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        int colcount = rsmd.getColumnCount();
        String[] colNames = new String[colcount];
        for (int i = 0; i < colcount; i++) {
            colNames[i] = rsmd.getColumnLabel(i + 1);
        }
        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
        while (rs.next()) {
            Map<String, Object> dataMap = new HashMap<String, Object>();
            for (int i = 0; i < colcount; i++) {
                dataMap.put(colNames[i], rs.getObject(i + 1));
            }
            dataList.add(dataMap);
        }

        rs.close();
        statement.close();
        conn.close();
        return dataList;
    }
————————————————————————————————
*******************************************************************************************************************

17:17 2012/3/18
用反射技术把结果集封装到对象
————————————————————————————————
    public static void main(String[] args) throws SQLException, Exception {
        User user = (User) getResult("SELECT id as Id,username as Username,money as Money FROM users WHERE id <20",User.class);
        System.out.println(user);
    }
    public static Object getResult(String sql,Class clazz)
            throws ClassNotFoundException, SQLException, Exception {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        String url = "jdbc:sqlserver://localhost:2644;DatabaseName=JDBCDemo";
        String user = "sa";
        String password = "123456";
        Object object = null;
        Connection conn = DriverManager.getConnection(url, user, password);
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        ResultSetMetaData rsmd = rs.getMetaData();
        int colcount = rsmd.getColumnCount();
        String[] colNames = new String[colcount];
        for (int i = 0; i < colcount; i++) {
            colNames[i] = rsmd.getColumnLabel(i + 1);
        }
        Method[] md = clazz.getMethods();
        if (rs.next()) {
            object = clazz.newInstance();
            for (int i = 0; i < colcount; i++) {
                String colName = colNames[i];
                String methodName = "set"+colName;
                for (Method m : md) {
                    if(methodName.equals(m.getName())){
                        m.invoke(object, rs.getObject(i+1));
                    }
                }
            }
        }

        rs.close();
        statement.close();
        conn.close();
        return object;
    }

————————————————————————————————
*******************************************************************************************************************

19:07 2012/3/18
创建连接池实现连接复用
方法，在程序启动时，给程序初始化10个连接并存于LinkList中，当用户调用时只需取走第一个连接，当用户占用完该连接后可把该连接返存到LinkList中。
*******************************************************************************************************************

21:44 2012/3/18
数据源和连接池
1、DataSource用来取代DriverManager来获取Connection;
2、通过DataSource内部会用一个连接池来缓存Connection，这样可以大幅度提高数据库的访问熟读；
3、连接池可以理解成一个能够存放Connection的Collection；
4、我们的程序只和DataSource打交道，不会直接访问连接池
*******************************************************************************************************************

22:03 2012/3/18
使用开源项目DBCP
需要三个包
1、commmons-collections-3.1.jar
2、commons-dbcp-1.2.2.jar
3、commons-pool.jar
*******************************************************************************************************************