Java数据库操作（JDBC方式连接及基本操作）

一、读取配置，判断环境，生成数据库信息对象

package com.jokin.learn.Database;

import com.mysql.jdbc.StringUtils;
import com.pajk.test.utils.ConfigProperty;

public class DBInfo {
    /*
     * 获取conn默认连接:根据用户project里的config.properties的runmode值--要么116开发库要么115测试库
     */
    public static String getConn(){
        // 默认连开发的库
        String conn = "jdbc:mysql://10.0.128.116:3306/?user=tddl&password=tddl";
        String runmode = ConfigProperty.get("runmode");
        if (!StringUtils.isNullOrEmpty(runmode)) {
            if (runmode.toLowerCase().startsWith("test")) {
                conn = "jdbc:mysql://10.0.128.115:3306/?user=tddl&password=tddl";
            }
        }
        System.out.println(conn);
        return conn;
    }

    /*
     * 获取conn连接:根据用户project里的config.properties的runmode值
     */
    public static String getConn(String connKey){
        String dbConn = ConfigProperty.get(connKey);
        System.out.println(dbConn);
        return dbConn;
    }
}

二、封装数据库操作曾删改查

package com.jokin.learn.Database;

import com.mysql.jdbc.StringUtils;
import com.pajk.test.utils.CommonUtil;
import org.omg.SendingContext.RunTime;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBOperate {
   private static String connectString = "";
   private static boolean isPrintSql = CommonUtil.isPrintSql();
   /**
    * 请先设置connectString
    */
   public static String insert(String insertSql){
      if (StringUtils.isNullOrEmpty(connectString))
      {
         connectString = DBInfo.getConn();
      }
      return insert(connectString, insertSql);
   }

   /**
    * 请先设置connectString
    */
   public static int execute(String executeSql){
      if (StringUtils.isNullOrEmpty(connectString))
      {
         connectString = DBInfo.getConn();
      }
      return execute(connectString, executeSql);
   }

   /**
    * 请先设置connectString
    */
   public static List<Map<String, Object>> select(String selectSql) {
      if (StringUtils.isNullOrEmpty(connectString))
      {
         connectString = DBInfo.getConn();
      }
      return select(connectString, selectSql);
   }

   /**
    * 请先设置connectString
    */
   public static Map<String, Object> selectOne(String selectSql) {
      if (StringUtils.isNullOrEmpty(connectString))
      {
         connectString = DBInfo.getConn();
      }
      return selectOne(connectString, selectSql);
   }
   
   /**
    * For insert sql connString 格式:
    * jdbc:mysql://localhost:3306/testdb?user=root&password=passwd
    */
   public static String insert(String connString, String insertSql) {
      if(isPrintSql) System.out.println(insertSql);
      Connection conn = null; // 定义一个MYSQL链接对象
      setConnection(connString);
      String ret_id = "";
      try {
         conn = getConnection();
         Statement stmt = conn.createStatement();

         // 新增一条数据
         stmt.executeUpdate(insertSql);
         ResultSet res = stmt.executeQuery("select LAST_INSERT_ID()");
         if (res.next()) {
            ret_id = res.getString(1);
         }

      } catch (Throwable th) {
         throw new RuntimeException("[Database.insert error]" + th.toString());
      } finally {
         if (null != conn)
            try {
               conn.close();
            } catch (SQLException e) {
               e.printStackTrace();
            }
      }
      return ret_id;
   }

   /**
    * For update or delete sql connString 格式:
    * jdbc:mysql://localhost:3306/testdb?user=root&password=passwd
    */
   public static int execute(String connString, String insertSql) {
      if(isPrintSql) System.out.println(insertSql);
      Connection conn = null; // 定义一个MYSQL链接对象
      setConnection(connString);
      int ret_id = 0;
      try {
         conn = getConnection();
         Statement stmt = conn.createStatement();

         ret_id = stmt.executeUpdate(insertSql);

      } catch (Throwable th) {
         throw new RuntimeException("[Database.update error]" + th.toString());
      } finally {
         if (null != conn)
            try {
               conn.close();
            } catch (SQLException e) {
               e.printStackTrace();
            }
      }
      return ret_id;
   }

   /**
    * For select sql connString 格式:
    * jdbc:mysql://localhost:3306/testdb?user=root&password=passwd
    */
   public static List<Map<String, Object>> select(String connString,
         String selectSql){
      List<Map<String, Object>> ll = new ArrayList<Map<String, Object>>();
      if(StringUtils.isNullOrEmpty(selectSql)) return ll;
      if(isPrintSql) System.out.println(selectSql);
      Connection conn = null; // 定义一个MYSQL链接对象
      setConnection(connString);
      try {
         conn = getConnection();
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(selectSql);
         ResultSetMetaData md = rs.getMetaData();
         int columns = md.getColumnCount();
         while (rs.next()) { // 循环输出结果集
            Map<String, Object> row = new HashMap<String, Object>();
            for (int i = 1; i <= columns; ++i) {
               row.put(md.getColumnLabel(i), rs.getString(i));
            }
            ll.add(row);
         }

      } catch (Throwable th) {
         throw new RuntimeException("[Database.select error]" + th.toString());
      } finally {
         if (null != conn)
            try {
               conn.close();
            } catch (SQLException e) {
               e.printStackTrace();
            }
      }
      return ll;
   }

   
   public static Map<String, Object> selectOne(String connString,
         String selectSql) {
      List<Map<String, Object>> ll = select(connString,selectSql);
      if(ll.size() > 0){
         return ll.get(0);
      }else{
         return new HashMap<String, Object>();
      }
   }
   
   /** 获取数据库连接的函数 */
   private static Connection getConnection() {
      Connection conn = null; // 创建用于连接数据库的Connection对象
      try {
            if(!StringUtils.isNullOrEmpty(connectString)) {
                if(connectString.toLowerCase().indexOf("postgresql") > 0){
                    Class.forName("org.postgresql.Driver"); //  PostGIS驱动
                }else{
                    Class.forName("com.mysql.jdbc.Driver").newInstance(); // MYSQL驱动
                }
                conn = DriverManager.getConnection(connectString);
            }
      } catch (Throwable th) {
         System.err.println("数据库连接失败" + th.getMessage());
         throw new RuntimeException("数据库连接失败" + th.getMessage());
      }
      return conn; // 返回所建立的数据库连接
   }

   public static void setConnection(String conn) {
      connectString = conn;
   }

   
   
   public  static  void main(String[] args){
        //mysql test
//     String conn = "jdbc:mysql://10.128.240.200:3306/?user=reader&password=reader";
//     try {
//       System.out.print("status========="+TDDLOperate.select(conn,"select status FROM user where id=1260609", 1260609l).get(0).get("status"));
//    } catch (Exception e) {
//       // TODO Auto-generated catch block
//       e.printStackTrace();
//    }

        //postGIS test
        String conn = "jdbc:postgresql://10.128.242.16:5432/?user=addrgis&password=addrgis";
        String sql = "select * from district_boundary limit 10;";
      List<Map<String, Object>> listMap = DBOperate.select(conn,sql);
      System.out.print("count="+listMap.size());

   }
}