java 數(shù)據(jù)庫連接與增刪改查操作實例詳解

更新時間：2019年11月20日 10:06:40 作者：傾聽歲月

這篇文章主要介紹了java 數(shù)據(jù)庫連接與增刪改查操作,結(jié)合實例形式詳細(xì)分析了java使用jdbc進(jìn)行數(shù)據(jù)庫連接及增刪改查等相關(guān)操作實現(xiàn)技巧與注意事項,需要的朋友可以參考下

本文實例講述了java 數(shù)據(jù)庫連接與增刪改查操作。分享給大家供大家參考，具體如下：

1、設(shè)置數(shù)據(jù)庫的基本配置信息

package mysql;
//數(shù)據(jù)庫配置資源連接
public final class DbConfig 
{
  //數(shù)據(jù)庫資源 指定所使用的數(shù)據(jù)庫類型 地址 端口 數(shù)據(jù)庫名稱 編碼格式
  public final static String dbUrl = "jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncodong=utf-8";
  //數(shù)據(jù)庫用戶名
  public final static String dbUser= "xxxxxx";
  //數(shù)據(jù)庫密碼
  public final static String dbPwd = "xxxxxx";
}

2、數(shù)據(jù)庫連接類用戶獲取數(shù)據(jù)庫連接

package mysql;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
public class Conn 
{
  //保存住默認(rèn)數(shù)據(jù)庫連接
  private static Connection conn = null;
  //數(shù)據(jù)庫連接
  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;
  }
  //獲得第三方數(shù)據(jù)庫鏈接
  public static Connection getConn(String dbUrl,String dbUser,String dbPwd)
  {
    return getDbConn(DbConfig.dbUrl,DbConfig.dbUser,DbConfig.dbPwd);
  }
  //獲得本地默認(rèn)數(shù)據(jù)庫連接
  public static Connection getConn()
  {
    if(conn == null){
      conn = getDbConn(DbConfig.dbUrl,DbConfig.dbUser,DbConfig.dbPwd);
    }
    return conn;
  }
}

3、數(shù)據(jù)庫測試類檢驗獲得的數(shù)據(jù)庫連接是否有效

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();
  }
  //自定義數(shù)據(jù)庫連接方式
  public static void index1()
  {
    //獲取數(shù)據(jù)庫連接
    Connection conn = Conn.getConn(DbConfig.dbUrl,DbConfig.dbUser,DbConfig.dbPwd);
    //測試該數(shù)據(jù)庫連接是否有效
    index3(conn);
  }
  //默認(rèn)方式獲取數(shù)據(jù)庫連接
  public static void index2()
  {
    //獲取數(shù)據(jù)庫連接
    Connection conn = Conn.getConn();
    //測試該數(shù)據(jù)庫連接是否有效
    index3(conn);
  }
  /**
   * 測試數(shù)據(jù)庫連接是否有效
   * @param
   * Connection conn 數(shù)據(jù)庫連接
   * */
  public static void index3(Connection conn)
  {
    //定義要執(zhí)行的sql語句
    String sql = "select * from table_name where id = ?";
    try
    {
      //對sql語句進(jìn)行預(yù)處理
      PreparedStatement pre = conn.prepareStatement(sql);
      //變量數(shù)據(jù)填充 填充后的sql為 select * from table_name where id = "xxxxxxx"
      pre.setString(1,"xxxxx");
      //執(zhí)行sql語句 發(fā)揮執(zhí)行的結(jié)果
      ResultSet result = pre.executeQuery();
      //返回的結(jié)果是否是空數(shù)據(jù)
      if(!result.next())
      {
        System.out.println("沒有查詢到響應(yīng)的數(shù)據(jù)");
        return;
      }
      //獲取返回結(jié)果的元數(shù)據(jù)，列名
      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();
    }
  }
}

獲取到Connection 連接后看到執(zhí)行一個sql語句獲得返回結(jié)果還要這么多的處理操作，以下是自己封裝的mysql操作類

數(shù)據(jù)庫接口類，針對數(shù)據(jù)庫操作的類，都得實現(xiàn)這個類。不管以后更換了什么數(shù)據(jù)庫，該數(shù)據(jù)庫的操作類都得實現(xiàn)這個接口所規(guī)定的方法，然后我們不需要任何的改動，只需要變更該接口的實現(xiàn)就可以了。

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
{
  /**
   * 數(shù)據(jù)庫連接
   * */
  public void setConnection(Connection conn);
  public Connection getConnection();
  /**
   * 查詢所有數(shù)據(jù)
   * @param 
   * String 要執(zhí)行的sql語句
   * @param 
   * String[] keyVal 映射數(shù)組
   * @return
   * List<Map<String,String>> result 查詢結(jié)果集
   * */
  public List<Map<String,String>> query(String sql,String[] keyVal);
  public List<Map<String,String>> query(String sql);
  /**
   * 查詢單行數(shù)據(jù)
   * @param 
   * String 要執(zhí)行的sql語句
   * @param 
   * String[] keyVal 映射數(shù)組
   * @return
   * Map<String,String> result 查詢結(jié)果集
   * */
  public Map<String,String> find(String sql,String[] keyVal);
  public Map<String,String> find(String sql);
  /**
   * 更新數(shù)據(jù)
   * @param 
   * String 要執(zhí)行的sql語句
   * @param 
   * String[] keyVal 映射數(shù)組
   * @return
   * int resultInt 受影響的行數(shù)
   * */
  public int update(String sql,String[] keyVal);
  public int update(String sql);
  /**
   * 新增數(shù)據(jù)
   * @param 
   * String 要執(zhí)行的sql語句
   * @param 
   * String[] keyVal 映射數(shù)組
   * @return
   * int resultInt 新增成功行數(shù)
   * */
  public int insert(String sql,String[] keyVal);
  public int insert(String sql);
  /**
   * 刪除數(shù)據(jù)庫
   * @param 
   * String 要執(zhí)行的sql語句
   * @param 
   * String[] keyVal 映射數(shù)組
   * @return
   * boolean 刪除時候成功
   * */
  public boolean delete(String sql,String[] keyVal);
  public boolean delete(String sql);
  /**
   * 調(diào)用存儲過程
   * @param
   * String callFunc 存儲過程名稱
   * List<Map<String,String>> 存儲過程參數(shù)值 如：Map<"int","22">
   * */
  public List<Map<String,String>> callResult(String callFunc,List<DataType> keyVal);
  public List<Map<String,String>> callResult(String callFunc);
}

針對DbOper接口的實現(xiàn)

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 操作實現(xiàn)類 - 容器
 * 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);
  }
  /**
   * 調(diào)用存儲過程
   * @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);
  }
  /**
   * 關(guān)閉資源鏈接
   * */
  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);
  }
}

以下用于調(diào)用存儲過程使用的工具類

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;
}

更多關(guān)于java相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《Java使用JDBC操作數(shù)據(jù)庫技巧總結(jié)》、《Java+MySQL數(shù)據(jù)庫程序設(shè)計總結(jié)》、《Java數(shù)據(jù)結(jié)構(gòu)與算法教程》、《Java文件與目錄操作技巧匯總》、《Java操作DOM節(jié)點技巧總結(jié)》和《Java緩存操作技巧匯總》

希望本文所述對大家java程序設(shè)計有所幫助。

您可能感興趣的文章: