java 数据库连接

最新推荐文章于 2023-05-01 19:31:25 发布

倾听岁月

最新推荐文章于 2023-05-01 19:31:25 发布

阅读量319

点赞数

分类专栏： java 文章标签：数据库 java mysql 编码

本文链接：https://blog.csdn.net/u014559227/article/details/72458308

版权

java 专栏收录该内容

34 篇文章 0 订阅

订阅专栏

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

倾听岁月

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
java 数据库连接

1、设置数据库的基本配置信息package mysql;//数据库配置资源连接public final class DbConfig { //数据库资源指定所使用的数据库类型地址端口数据库名称编码格式 public final static String dbUrl = "jdbc:mysql://localhost:3306/database_name?useUnic
复制链接

扫一扫

专栏目录