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

本文实例讲述了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);
  }
  
  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();
  
  public List> query(String sql,String[] keyVal);
  public List> query(String sql);
  
  public Map find(String sql,String[] keyVal);
  public Map find(String sql);
  
  public int update(String sql,String[] keyVal);
  public int update(String sql);
  
  public int insert(String sql,String[] keyVal);
  public int insert(String sql);
  
  public boolean delete(String sql,String[] keyVal);
  public boolean delete(String sql);
  
  public List> callResult(String callFunc,List keyVal);
  public List> 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;

@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> 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> list = new ArrayList>(result.getRow());
 result.first();
 int propertiesLength = meta.getColumnCount();
 do{
   Map map = new HashMap(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> query(String sql)
  {
    return query(sql,null);
  }
  @Override
  public Map 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 map = new HashMap(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 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);
  }
  
  public List> callResult(String callFunc,List 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> list = new ArrayList>(result.getRow());
      result.first();
      int propertiesLength = meta.getColumnCount();
      do{
 Map map = new HashMap(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> 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相关内容感兴趣的读者可查看本站专题：《Java使用JDBC操作数据库技巧总结》、《Java+MySQL数据库程序设计总结》、《Java数据结构与算法教程》、《Java文件与目录操作技巧汇总》、《Java操作DOM节点技巧总结》和《Java缓存操作技巧汇总》

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