java 数据库连接与增删改查操作实例详解

package mysql;//数据库配置资源连接public final class DbConfig {  //数据库资源 指定所使用的数据库类型 地址 端口 数据库名称 编码格式  public final static String dbUrl = "jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncodong=utf-8";  //数据库用户名  public final static String dbUser= "xxxxxx";  //数据库密码  public final static String dbPwd = "xxxxxx";}

package mysql;import java.sql.SQLException;import java.sql.Connection;import java.sql.DriverManager;public class Conn {  //保存住默认数据库连接  private static Connection conn = null;  //数据库连接  private static Connection getDbConn(String dbUurl,String dbUser,String dbPwd)  {    Connection dbConn;    try{      //载入mysql 工具包      Class.forName("com.mysql.jdbc.Driver");      dbConn = DriverManager.getConnection(dbUurl,dbUser,dbPwd);    }catch(ClassNotFoundException | SQLException e){      dbConn = null;      e.printStackTrace();    }    return dbConn;  }  //获得第三方数据库链接  public static Connection getConn(String dbUrl,String dbUser,String dbPwd)  {    return getDbConn(DbConfig.dbUrl,DbConfig.dbUser,DbConfig.dbPwd);  }  //获得本地默认数据库连接  public static Connection getConn()  {    if(conn == null){      conn = getDbConn(DbConfig.dbUrl,DbConfig.dbUser,DbConfig.dbPwd);    }    return conn;  }}

import Demo.Demo;import mysql.*;import java.sql.Connection;import java.sql.SQLException;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.PreparedStatement;public class Index {  public static void main(String[] args)  {    index1();    //分隔符    System.out.println("----------------------------------------------");    System.out.println("----------------------------------------------");    System.out.println("----------------------------------------------");    index2();  }  //自定义数据库连接方式  public static void index1()  {    //获取数据库连接    Connection conn = Conn.getConn(DbConfig.dbUrl,DbConfig.dbUser,DbConfig.dbPwd);    //测试该数据库连接是否有效    index3(conn);  }  //默认方式获取数据库连接  public static void index2()  {    //获取数据库连接    Connection conn = Conn.getConn();    //测试该数据库连接是否有效    index3(conn);  }  /**   * 测试数据库连接是否有效   * @param   * Connection conn 数据库连接   * */  public static void index3(Connection conn)  {    //定义要执行的sql语句    String sql = "select * from table_name where id = ?";    try    {      //对sql语句进行预处理      PreparedStatement pre = conn.prepareStatement(sql);      //变量数据填充 填充后的sql为 select * from table_name where id = "xxxxxxx"      pre.setString(1,"xxxxx");      //执行sql语句 发挥执行的结果      ResultSet result = pre.executeQuery();      //返回的结果是否是空数据      if(!result.next())      {        System.out.println("没有查询到响应的数据");        return;      }      //获取返回结果的元数据，列名      ResultSetMetaData meta = result.getMetaData();      //打印输出      int metaLength = meta.getColumnCount();      do{        for(int forInt = 1;forInt <= metaLength;forInt++)        {          String keyName = meta.getColumnName(forInt);          System.out.println(keyName + " => " + result.getString(keyName));        }      }while(result.next());         }catch(SQLException e){      e.printStackTrace();    }  }}

package standard.db.operation;import java.sql.Connection;import java.util.List;import java.util.Map;import spring.beans.db.realization.mysql.DataType;public interface DbOper{  /**   * 数据库连接   * */  public void setConnection(Connection conn);  public Connection getConnection();  /**   * 查询所有数据   * @param    * String 要执行的sql语句   * @param    * String[] keyVal 映射数组   * @return   * List<Map<String,String>> result 查询结果集   * */  public List<Map<String,String>> query(String sql,String[] keyVal);  public List<Map<String,String>> query(String sql);  /**   * 查询单行数据   * @param    * String 要执行的sql语句   * @param    * String[] keyVal 映射数组   * @return   * Map<String,String> result 查询结果集   * */  public Map<String,String> find(String sql,String[] keyVal);  public Map<String,String> find(String sql);  /**   * 更新数据   * @param    * String 要执行的sql语句   * @param    * String[] keyVal 映射数组   * @return   * int resultInt 受影响的行数   * */  public int update(String sql,String[] keyVal);  public int update(String sql);  /**   * 新增数据   * @param    * String 要执行的sql语句   * @param    * String[] keyVal 映射数组   * @return   * int resultInt 新增成功行数   * */  public int insert(String sql,String[] keyVal);  public int insert(String sql);  /**   * 删除数据库   * @param    * String 要执行的sql语句   * @param    * String[] keyVal 映射数组   * @return   * boolean 删除时候成功   * */  public boolean delete(String sql,String[] keyVal);  public boolean delete(String sql);  /**   * 调用存储过程   * @param   * String callFunc 存储过程名称   * List<Map<String,String>> 存储过程参数值 如：Map<"int","22">   * */  public List<Map<String,String>> callResult(String callFunc,List<DataType> keyVal);  public List<Map<String,String>> callResult(String callFunc);}

package spring.beans.db.realization.mysql;import java.sql.Connection;import java.util.List;import java.util.ArrayList;import java.util.Map;import java.util.HashMap;import java.sql.CallableStatement; import java.sql.SQLException;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import javax.annotation.PostConstruct;import javax.annotation.PreDestroy;import javax.sql.DataSource;import org.springframework.beans.factory.annotation.Value;import org.springframework.context.annotation.Scope;import org.springframework.stereotype.Repository;import standard.db.operation.DbOper;/** * mysql 操作实现类 - 容器 * author : 谭勇 * create_date : 2017-04-13 * */@Repository("db_connection")@Scope("request")public final class MysqlRealization implements DbOper{  private Connection conn;  /**   * 对象初始化   * */  @PostConstruct  public void initDb()  {  }  @Override  public void setConnection(Connection conn)  {    this.conn = conn;  }  @Value(value="#{mysql_driver_manager_dataSource}")  public void setConnection(DataSource dataSource)  {    try{      setConnection(dataSource.getConnection());    }catch(SQLException e)    {    }  }  @Override  public Connection getConnection()  {    return this.conn;  }  @Override  public List<Map<String, String>> query(String sql, String[] keyVal)  {    PreparedStatement pre = null;    ResultSet result = null;    ResultSetMetaData meta = null;    try{      pre = conn.prepareStatement(sql);      if(keyVal != null)      {        //映射到问号        for(int i=1;i<=keyVal.length;i++)        {          pre.setString(i, keyVal[i-1]);        }      }      result = pre.executeQuery();      if (result.next())      {        meta = result.getMetaData();        result.last();        List<Map<String,String>> list = new ArrayList<Map<String,String>>(result.getRow());        result.first();        int propertiesLength = meta.getColumnCount();        do{          Map<String,String> map = new HashMap<String,String>(propertiesLength);          for(int i=1;i<=propertiesLength;i++)          {            String keyName = meta.getColumnName(i);            map.put(keyName, result.getString(keyName));          }          list.add(map);        }while(result.next());        return list;      }    }catch(SQLException e)    {      e.printStackTrace();    }finally{      closePreparedStatement(pre);      closeResultSet(result);    }    return null;  }  @Override  public List<Map<String, String>> query(String sql)  {    return query(sql,null);  }  @Override  public Map<String, String> find(String sql, String[] keyVal)   {    PreparedStatement pre = null;    ResultSet result = null;    ResultSetMetaData meta = null;    try{      pre = conn.prepareStatement(sql);      if(keyVal != null)      {        //映射到问号        for(int i=1;i<=keyVal.length;i++)        {          pre.setString(i, keyVal[i-1]);        }      }      result = pre.executeQuery();      if (result.next())      {        meta = result.getMetaData();        int propertiesLength = meta.getColumnCount();        Map<String,String> map = new HashMap<String,String>(propertiesLength);        for(int i=1;i<=propertiesLength;i++)        {          String keyName = meta.getColumnName(i);          map.put(keyName, result.getString(keyName));        }        return map;      }    }catch(SQLException e)    {      e.printStackTrace();    }finally{      closePreparedStatement(pre);      closeResultSet(result);    }    return null;  }  @Override  public Map<String, String> find(String sql)   {    return find(sql,null);  }  @Override  public int update(String sql, String[] keyVal)  {    PreparedStatement pre = null;    try{      pre = conn.prepareStatement(sql);      if(keyVal != null)      {        //映射到问号        for(int i=1;i<=keyVal.length;i++)        {          pre.setString(i, keyVal[i-1]);        }      }      return pre.executeUpdate();    }catch(SQLException e)    {      e.printStackTrace();    }finally{      closePreparedStatement(pre);    }    return 0;  }  @Override  public int update(String sql)  {    return update(sql,null);  }  @Override  public int insert(String sql, String[] keyVal)  {    PreparedStatement pre = null;    try{      pre = conn.prepareStatement(sql);      if(keyVal != null)      {        //映射到问号        for(int i=1;i<=keyVal.length;i++)        {          pre.setString(i, keyVal[i-1]);        }      }      return pre.executeUpdate();    }catch(SQLException e)    {      e.printStackTrace();    }finally{      closePreparedStatement(pre);    }    return 0;  }  @Override  public int insert(String sql)  {    return insert(sql,null);  }  @Override  public boolean delete(String sql, String[] keyVal)  {    PreparedStatement pre = null;    try{      pre = conn.prepareStatement(sql);      if(keyVal != null)      {        //映射到问号        for(int i=1;i<=keyVal.length;i++)        {          pre.setString(i, keyVal[i-1]);        }      }      return pre.executeUpdate() > 0 ? true:false;    }catch(SQLException e)    {      e.printStackTrace();    }finally{      closePreparedStatement(pre);    }    return false;  }  @Override  public boolean delete(String sql)  {    return delete(sql,null);  }  /**   * 调用存储过程   * @param   * String callFunc 存储过程名   * */  public List<Map<String,String>> callResult(String callFunc,List<DataType> keyVal)  {    String call = "{call " + callFunc + "}";    ResultSetMetaData meta = null;    CallableStatement callableStatement= null;    ResultSet result = null;    try{      callableStatement = conn.prepareCall(call);      if(keyVal != null)      {        for(int i=1;i<=keyVal.size();i++)        {          DataType data = keyVal.get(i-1);          switch(data.getType())          {            case ValueTypeSource.STRING:              callableStatement.setString(i, String.valueOf(data.getValue()));            break;            case ValueTypeSource.INT:              callableStatement.setInt(i, Integer.valueOf(data.getValue()));            break;            case ValueTypeSource.LONG:              callableStatement.setLong(i, Long.valueOf(data.getValue()));            break;            case ValueTypeSource.DOUBLE:              callableStatement.setDouble(i, Double.valueOf(data.getValue()));            break;            default:              callableStatement.setString(i,String.valueOf(data.getValue()));          }        }      }      callableStatement.execute();      result = callableStatement.getResultSet();      meta = result.getMetaData();      result.last();      List<Map<String,String>> list = new ArrayList<Map<String,String>>(result.getRow());      result.first();      int propertiesLength = meta.getColumnCount();      do{        Map<String,String> map = new HashMap<String,String>(propertiesLength);        for(int i=1;i<=propertiesLength;i++)        {          String keyName = meta.getColumnName(i);          map.put(keyName, result.getString(keyName));        }        list.add(map);      }while(result.next());      return list;    }catch(SQLException e)    {      e.printStackTrace();      return null;    }finally{      closeCallableStatement(callableStatement);      closeResultSet(result);    }  }  @Override  public List<Map<String,String>> callResult(String callFunc)  {    return callResult(callFunc,null);  }  /**   * 关闭资源链接   * */  private void closePreparedStatement(PreparedStatement pre)  {    if(pre != null)    {      try      {        pre.close();      }catch(SQLException e)      {        e.printStackTrace();      }    }  }  private void closeResultSet(ResultSet result)  {    if(result != null)    {      try      {        result.close();      }catch(SQLException e)      {        e.printStackTrace();      }    }  }  private void closeCallableStatement(CallableStatement call)  {    if(call != null)    {      try      {        call.close();      }catch(SQLException e)      {        e.printStackTrace();      }    }  }  private void closeConnection(Connection conn)  {    if(conn != null)    {      try      {        conn.close();      }catch(SQLException e)      {        e.printStackTrace();      }    }  }  /**   * 对象注销   * */  @PreDestroy  public void closeDb()  {    closeConnection(conn);  }}

package spring.beans.db.realization.mysql;public final class DataType {  private String keyName;  private String value;  private int type;  public DataType(){}  public DataType(String keyName,String value,int type)  {    setKeyName(keyName);    setValue(value);    setType(type);  }  public void setKeyName(String keyName)  {    this.keyName = keyName;  }  public void setValue(String value)  {    this.value = value;  }  public void setType(int type)  {    this.type = type;  }  public String getKeyName()  {    return keyName;  }  public String getValue()  {    return value;  }  public int getType()  {    return type;  }}

package spring.beans.db.realization.mysql;public enum ValueType {  INT(ValueTypeSource.INT),  STRING(ValueTypeSource.STRING),  DOUBLE(ValueTypeSource.DOUBLE),  CHAR(ValueTypeSource.CHAR),  DATE(ValueTypeSource.DATE),  BLOB(ValueTypeSource.BLOB),  LONG(ValueTypeSource.LONG);  private int type;  private ValueType(int type)  {    this.type = type;  }  public int getType()  {    return type;  }}

package spring.beans.db.realization.mysql;public final class ValueTypeSource {  public final static int INT=1,              STRING=2,              DOUBLE=3,              CHAR=4,              DATE=5,              LONG=6,              BLOB=7;}