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

时间:2021-04-02 人气:0

本文实例讲述了java 数据库连接与增删改查操作。分享给大家供大家参考，具体如下：

1、设置数据库的基本配置信息

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

2、数据库连接类用户获取数据库连接

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

3、数据库测试类检验获得的数据库连接是否有效

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

获取到Connection 连接后看到执行一个sql语句获得返回结果还要这么多的处理操作，以下是自己封装的mysql操作类

数据库接口类，针对数据库操作的类，都得实现这个类。不管以后更换了什么数据库，该数据库的操作类都得实现这个接口所规定的方法，然后我们不需要任何的改动，只需要变更该接口的实现就可以了。

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

针对DbOper接口的实现

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

希望本文所述对大家java程序设计有所帮助。

您可能感兴趣的文章:

加载全部内容