JDBC使用总结

JAVA链接数据库并操作数据库的步骤：

1、新建jdbc.properties文件：

oracleDriver=oracle.jdbc.driver.OracleDriver
oracleURL=jdbc:oracle:thin:@192.168.1.82:1521:orcl
oracleUser=root
oraclePwd=root

2、链接数据库工具类

public  class DBConnectorUtil {
    public static InputStream in = null;
    public static String driverClass = "";
    public static String jdbcUrl ="";
    public static String user ="";
    public static String password ="";
    public static DBConnectorUtil DButil= new DBConnectorUtil();

    public static DBConnectorUtil Instance()
    {
        return DButil;
    }
    //构造获取配置文件中的值
    private DBConnectorUtil(){
        try{
            in = DBConnectorUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");//src/main/resources/
            Properties prop = new Properties();
            prop.load(in);
            driverClass = prop.getProperty("oracleDriver");
            jdbcUrl = prop.getProperty("oracleURL");
            user = prop.getProperty("oracleUser");
            password = prop.getProperty("oraclePwd");
        }catch (Exception e) {
            if (in==null){
                try{
                    in.close();
                }catch ( Exception e1){

                }
            }
            throw new ExceptionInInitializerError(e);
        }
    }
    /*获取数据库连接 */
    public static Connection getDBConn(){
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            System.out.println("配置加载类出错："+ driverClass);
            e.printStackTrace();
        }
        Connection con = null;
        try {
            con = DriverManager.getConnection(jdbcUrl, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("获取连接对象错误："+ jdbcUrl+"---"+ user+"---"+ password);
        }
        return con;
    }
    /*关闭数据库连接*/
    public static void closeCon(Connection con) throws SQLException {
        if (con != null)
            con.close();
    }
}

3、操作数据库（数据的 增 删 改 查 ），将返回数据封装到集合中

public class DBOperation {
    /**
     * 查询数据
     */
    public static ResultSet selectXKExteData4ORCL (){
        try{
            //连数据库  查询数据
            Connection conn = DBConnectorUtil.getDBConn();
            Statement stmt = conn.createStatement();
            String sql = "select ENG_ID,ENG_MODEL,ENG_REF from T_ENGINE order by ENG_ID desc";
            ResultSet resultSet = stmt.executeQuery(sql);
            return resultSet;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    public static void main(String[] args) {
        ResultSet resultSet = DBOperation.selectXKExteData4ORCL();
        while(resultSet.next()){
            int eng_id = resultSet.getInt("ENG_ID");
            String eng_model = resultSet.getString("ENG_MODEL");
            String eng_ref = resultSet.getString("ENG_REF");
            if(DataUtil.isNotEmpty(eng_ref)){
                Map<Integer, DutyCyceBean> engineAlgorithmInfoTempMap = new LinkedHashMap<Integer, DutyCyceBean>();
                List<DutyCyceBean> engineAlgorithmInfolist = DutyCyceBean.getList(eng_ref);
                if(null!= engineAlgorithmInfolist){
                    for (DutyCyceBean engineAlgorithmInfo : engineAlgorithmInfolist) {
                        engineAlgorithmInfoTempMap.put(engineAlgorithmInfo.getRev(), engineAlgorithmInfo);
                    }
                }
                engineAlgorithmMap.put(eng_id, engineAlgorithmInfoTempMap);
            }
        }
    }
}

 4、查询数据库（数据的 查 ），将返回数据封装到Bean中

引用commons-dbutils工具，方便简洁

 <dependency>
      　　　<groupId>commons-dbutils</groupId>
           <artifactId>commons-dbutils</artifactId>
           <version>1.7</version>
 </dependency>

测试类，调用时需要sql返回的字段名和bean中属性名相同即可，简单类型可自动转换

public class OracleQueryCommon<T> {
    private static volatile OracleQueryCommon instance;
    private OracleQueryCommon(){}
    public static OracleQueryCommon getInstance(){
        if(instance == null){
            synchronized (OracleQueryCommon.class){
                if(instance == null){
                    instance = new OracleQueryCommon();
                }
            }
        }
        return instance;
    }

    public List<T> getDataList(String sql,Class cls){
        List<T> listResult = new ArrayList<>();
        try{
            Connection conn = AnalysisDBConnectorUtil.getDBConn();
            QueryRunner qRunner = new QueryRunner();
            listResult = (List<T>) qRunner.query(conn, sql,new BeanListHandler(cls));
            //关闭数据库连接
            DbUtils.closeQuietly(conn);
        }catch (Exception e){
            e.printStackTrace();
        }
        return listResult;
    }

    public T getData(String sql,Class cls){
        T result = null;
        try{
            Connection conn = AnalysisDBConnectorUtil.getDBConn();
            QueryRunner qRunner = new QueryRunner();
            result = (T) qRunner.query(conn, sql,new BeanHandler(cls));
            //关闭数据库连接
            DbUtils.closeQuietly(conn);
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }

    public static void main(String[] args) {
        String sql = "select T.C,t.V from table T WHERE t.C IS NOT NULL AND SUBSTR(UPPER(T.V),2,1) = 'B'";
        List<Bean> dataList = new OracleQueryCommon<Bean>().getDataList(sql, Bean.class);
        System.out.println(dataList.toString());
    }
}

 

注意:JDBC中Statement 接口提供了三种执行 SQL 语句的方法：executeQuery、executeUpdate 和 execute。使用哪一个方法由 SQL 语句所产生的内容决定。