Android 之JDBC

JDBC(Java DataBase Connectivity)是使用 Java 存取数据库系统的标准解决方案，它将不同数据库间各自差异API与标准 SQL语句分开看待，实现数据库无关的 Java操作接口。

JDBC程序访问数据库的步骤 

步骤一：加载JDBC驱动程序:Class.forName("com.mysql.jdbc.Driver");Oracle10g：oracle.jdbc.driver.OracleDriver;MySQL5：com.mysql.jdbc.Driver;SQLServer2005：com.microsoft.sqlserver.jdbc.SQLServerDriver

步骤二：提供连接参数:URL,USERNAME, PASSWORD:不同数据库产品的连接URL不同

Oracle10g：jdbc:oracle:thin:@主机名:端口:数据库SID

jdbc:oracle:thin:@localhost:1521:ORCL

MySQL5：jdbc:mysql://主机名:端口/数据库名

jdbc:mysql://localhost:3306/test       

SQLServer2005：jdbc:sqlserver://主机名:端口:DatabaseName=库名

jdbc:sqlserver://localhost:1433:DatabaseName=BookDB

步骤三：建立一个数据库的连接:connection=DriverManager.getConnection(URL,USERNAME, PASSWORD);

步骤四：创建一个statement:preparedStatement=connection.prepareStatement(sql);

步骤五：执行SQL语句:result=preparedStatement.executeUpdate();

步骤六：处理结果:flag=result>0?true:false;return flag;

步骤七：关闭JDBC对象:if(resultSet!=null){resultSet.close();}if(preparedStatement!=null){preparedStatement.close();}if(connection!=null){connection.close();}

package com.jdbc.dbUtils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.jdbc.dbUtils.dormain.UserInfo;



public class JDBCutils {

    private final String USERNAME="root";
    private final String PASSWORD="admin";
    private final String DRIVER="com.mysql.jdbc.Driver";
    private final String URL="jdbc:mysql://localhost:3306/mydb";
     
    private Connection connection;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;
    
    public JDBCutils() {
        try {
            Class.forName(DRIVER);//注册驱动
            System.out.println("成功注册驱动");
            
        } catch (Exception e) {
            // TODO: handle exception
        }
    }

    public Connection getConnection(){
        try {
            connection=DriverManager.getConnection(URL,USERNAME, PASSWORD);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return connection;
    }
    //增删改
    public boolean updateByPreparedStatement(String sql,List<Object> params)throws SQLException {
        boolean flag=false;
        int result=-1;//执行增删改操作的时候所影响的行数
        int index=1;//占位符的第一个位置
        preparedStatement=connection.prepareStatement(sql);
        if(params!=null&&!params.isEmpty()){
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(index++, params.get(i));
            }
        }
        result=preparedStatement.executeUpdate();
        flag=result>0?true:false;
        return flag;
    }
    //查询 返回单条记录
    public Map<String,Object> findSimpleResult(String sql,List<Object> params) throws SQLException{
        Map<String,Object> map=new HashMap<String,Object>();
        int index=1;//占位符的第一个位置
        preparedStatement=connection.prepareStatement(sql);
        if(params!=null&&!params.isEmpty()){
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(index++, params.get(i));
            }
        }
        resultSet=preparedStatement.executeQuery();
        ResultSetMetaData resultSetMetaData=resultSet.getMetaData();
        int col_len=resultSetMetaData.getColumnCount();//得到数据库表的列
        while(resultSet.next()){
            for (int i = 0; i < col_len; i++) {
                String col_name=resultSetMetaData.getColumnName(i+1);
                Object col_val=resultSet.getObject(col_name);
                if(col_val==null){
                    col_val="";
                }
                map.put(col_name,col_val);
            }
        }
        return map;
        
    }
    //查询 返回多条记录
    public List<Map<String,Object>> findMultiResult(String sql,List<Object> params) throws SQLException{
        List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
        int index=1;//占位符的第一个位置
        preparedStatement=connection.prepareStatement(sql);
        if(params!=null&&!params.isEmpty()){
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(index++, params.get(i));
            }
        }
        resultSet=preparedStatement.executeQuery();
        ResultSetMetaData resultSetMetaData=resultSet.getMetaData();
        int col_len=resultSetMetaData.getColumnCount();//得到数据库表的列名
        while(resultSet.next()){
            Map<String,Object>map=new HashMap<String,Object>();
            for (int i = 0; i < col_len; i++) {
                String col_name=resultSetMetaData.getColumnName(i+1);
                Object col_val=resultSet.getObject(col_name);
                if(col_val==null){
                    col_val="";
                }
                map.put(col_name,col_val);
            }
            list.add(map);
        }
        return list;
        
    }
    //jdbc的封装也可以采用反射机制
    public <T> T findSimpReflectResult(String sql,List<Object> params,Class<T> cls) throws Exception{
        T resObj=null;
        int index=1;//占位符的第一个位置
        preparedStatement=connection.prepareStatement(sql);
        if(params!=null&&!params.isEmpty()){
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(index++, params.get(i));
            }
        }
        resultSet=preparedStatement.executeQuery();
        ResultSetMetaData resultSetMetaData=resultSet.getMetaData();
        int col_len=resultSetMetaData.getColumnCount();//得到数据库表的列名
        while(resultSet.next()){
            resObj=cls.newInstance();//通过反射机制创建实例
            for (int i = 0; i < col_len; i++) {
                String col_name=resultSetMetaData.getColumnName(i+1);
                Object col_val=resultSet.getObject(col_name);
                if(col_val==null){
                    col_val="";
                }
                Field field=cls.getDeclaredField(col_name);
                field.setAccessible(true);//打开访问javabean的私有权限
                field.set(resObj, col_val);
            }
        }
        return resObj;
    }
    public <T> List<T> findMultiReflectResult(String sql,List<Object> params,Class<T> cls) throws Exception{
        List<T> list=new ArrayList<T>();
        int index=1;//占位符的第一个位置
        preparedStatement=connection.prepareStatement(sql);
        if(params!=null&&!params.isEmpty()){
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(index++, params.get(i));
            }
        }
        resultSet=preparedStatement.executeQuery();
        ResultSetMetaData resultSetMetaData=resultSet.getMetaData();
        int col_len=resultSetMetaData.getColumnCount();//得到数据库表的列名
        while(resultSet.next()){
            T resObj=cls.newInstance();//通过反射机制创建实例
            for (int i = 0; i < col_len; i++) {
                String col_name=resultSetMetaData.getColumnName(i+1);
                Object col_val=resultSet.getObject(col_name);
                if(col_val==null){
                    col_val="";
                }
                Field field=cls.getDeclaredField(col_name);
                field.setAccessible(true);//打开访问javabean的私有权限
                field.set(resObj, col_val);
            }
            list.add(resObj);
        }
        return list;
    }
    public void releaseConnection() throws SQLException{
        if(resultSet!=null){
            resultSet.close();
        }
        if(preparedStatement!=null){
            preparedStatement.close();
        }
        if(connection!=null){
            connection.close();
        }
    }
    public static void main(String[] args) throws Exception {
        // TODO Auto-generated method stub
        JDBCutils jdbc=new JDBCutils();
        jdbc.getConnection();
        List<Object> params=new ArrayList<Object>();
    
        /*    params.add("fjf");
        params.add("123");
        params.add("fanjingfang");
        String sql="insert into userinfo(username,password,realname)values(?,?,?)";
        boolean flag=jdbc.updateByPreparedStatement(sql, params);
        System.out.println(flag);
        params.add(1);
        String sql="select * from userinfo where id=?";
        Map<String,Object> m=jdbc.findSimpleResult(sql, params);
        System.out.println(m);*/
        
        /*String sql2="select * from userinfo ";
        List<Map<String,Object>> list=jdbc.findMultiResult(sql2, null);
        System.out.println(list);*/
        
    /*    params.add(1);
        String sql3="select * from userinfo where id=? ";
        UserInfo u=jdbc.findSimpReflectResult(sql3, params,UserInfo.class);
        System.out.println(u);*/
        
        String sql4="select * from userinfo ";
        List<UserInfo> list=jdbc.findMultiReflectResult(sql4, null,UserInfo.class);
        System.out.println(list);
    }

}