java JDBCUtils 使用静态代码块连接数据库全程值保持一次连接

本文链接：https://blog.youkuaiyun.com/webxscan/article/details/134664651

业务逻辑层

\src\com\data\service\UserService.java

\src\com\data\service\impl\UserServiceImpl.java

DAO接口层

\src\com\user\dao\UserDao.java
\src\com\user\dao\impl\Userimpl.java

\src\com\data\entity\JDBCUtils.java

使用静态代码块连接数据库全程值保持一次连接

package com.data.entity;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

public class JDBCUtils {
//public static DataSource dataSource=null;
public static JdbcTemplate jdbcTemplate=null;
static {  //静态代码块
    //方法1
//    String url = "jdbc:mysql://localhost:3306/xx?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
//    String username = "root";
//    String password = "1234567";
//    DataSource dataSource = new DriverManagerDataSource(url,username,password);
//    jdbcTemplate = new JdbcTemplate(dataSource);

    //方法2
    Mysql_Template_conn conn=new Mysql_Template_conn(
            "localhost","root","123456","data","3306","UTF-8");
    conn.getConnection();  // 建立连接
    jdbcTemplate = new JdbcTemplate(conn.conn);

}

    public static JdbcTemplate newJdbcTemplate(){
        return jdbcTemplate;
    }

    public static JdbcTemplate newJdbcTemplate2(){
        return jdbcTemplate;
    }




}

\src\com\data\entity\Mysql_conn.java

package com.data.entity;

import java.sql.*;
/*
JDBC封装增删改查
*QQ 7650371
* */
public class Mysql_conn {
    private Connection con; // 声明Connection对象
    private Statement ps;  //PreparedStatement
    private ResultSet rs;
    public boolean link=false;  // static
    public String HOSTNAME=""; //服务器
    public String USERNAME=""; //数据库登录名
    public  String PASSWORD=""; //密码
    public String DATABASE=""; //数据库名
    public String HOSTPORT=""; //端口
    public String CHARSET=""; //编码

    public Mysql_conn(String HOSTNAME, String USERNAME, String PASSWORD, String DATABASE) {
        this.HOSTNAME = HOSTNAME;
        this.USERNAME = USERNAME;
        this.PASSWORD = PASSWORD;
        this.DATABASE = DATABASE;
    }

    public Mysql_conn(String HOSTNAME, String USERNAME, String PASSWORD, String DATABASE, String HOSTPORT) {
        this.HOSTNAME = HOSTNAME;
        this.USERNAME = USERNAME;
        this.PASSWORD = PASSWORD;
        this.DATABASE = DATABASE;
        this.HOSTPORT = HOSTPORT;
    }

    public Mysql_conn(String HOSTNAME, String USERNAME, String PASSWORD, String DATABASE, String HOSTPORT, String CHARSET) {
        this.HOSTNAME = HOSTNAME;
        this.USERNAME = USERNAME;
        this.PASSWORD = PASSWORD;
        this.DATABASE = DATABASE;
        this.HOSTPORT = HOSTPORT;
        this.CHARSET = CHARSET;
    }

    public Connection getConnection() { // 建立返回值为Connection的方法
//        HOSTNAME = "localhost";  //服务器
//        USERNAME = "root";//数据库登录名
//        PASSWORD = "218484";//密码
//        DATABASE = "data";//数据库名
        if(HOSTPORT==""){HOSTPORT = "3306";} //端口
        if(CHARSET==""){CHARSET = "UTF-8";} //编码

        try { // 加载数据库驱动类
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("数据库驱动加载成功");
            try { // 通过访问数据库的URL获取数据库连接对象
//                jdbc:mysql	协议
//                localhost:3306	localhost是本机地址127.0.0.1 ， 3306 端口名，是mysql开启的服务，如果上述（配置一）的mysql服务未开启，会报 com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure 的异常
//                databaseName	数据库的名字，如果没有此数据库会报SQLSyntaxErrorException: Unknown database 'xxx'，的异常
//                useSSL=false	在web领域要用到，指是否开启ssl安全连接，但MySQL 8.0 以上版本不需要建立 SSL 连接，需要关闭。
//                serverTimezone=UTC	设置时区
//                characterEncoding=UTF-8	设置编码格式，不设置很可能造成乱码。
                //"jdbc:mysql://localhost:3306/xx2304?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai"
                String jdbc="jdbc:mysql://"+HOSTNAME+":"+HOSTPORT+"/"+DATABASE+"?useSSL=false&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding="+CHARSET;
                this.con = DriverManager.getConnection(jdbc, USERNAME, PASSWORD);
                this.ps = this.con.createStatement();
                System.out.println("数据库连接成功");
                this.link=true;
            } catch (SQLException e) {
                e.printStackTrace();
                this.link=false;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            this.link=false;
        }
        return con; // 按方法要求返回一个Connection对象
    }

    public  void close(){
        try {
            if(this.con != null) {this.con.close();}
        } catch (Exception e) {
            System.out.println(e.toString());
        }
        try {
            if(this.ps != null) {this.ps.close();}
        } catch (Exception e) {
            System.out.println(e.toString());
        }
        try {
            if(this.rs != null) {this.rs.close();}
        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }

//    execute	可以增删改查
//    executeUpdate	可以增删改，但是不能查询
//    executeQuery	只可以查询
    public void executeQuery(String sql){  //只可以查询
        try {
            this.rs = this.ps.executeQuery("SELECT *FROM jobs");
            while(this.rs.next()) {
                String title=this.rs.getString("job_title");
                System.out.println("====="+title);

                //Integer XX=this.rs.getInt();
                // long xx=this.rs.getLong();
//                double xx=this.rs.getDouble();
//                String xx=this.rs.getString();
//                Date xx=this.rs.getDate();
            }
        //Statement statement = this.con.createStatement();
//        ResultSet resultSet = statement.executeQuery("SELECT *FROM jobs");
//        while(resultSet.next()){
//            System.out.println("====="+resultSet.getString("job_title"));
//        }
//        resultSet.close();
//        //statement.close();
        } catch (Exception e) {
            System.out.println(e.toString());
        }
    }

    public int executeUpdate(String sql){   //可以增删改，但是不能查询
        try {
//    String insertData = "INSERT into jobs(job_id,job_title) values('xccxxx','ffccfffffff')";
            //Statement statement = this.con.createStatement();
            //statement.close();
            return this.ps.executeUpdate(sql);  //添加
        } catch (Exception e) {
            System.out.println(e.toString());
            return 0;
        }
    }

    public boolean execute(String sql) {   //可以增删改查
        try {
            return this.ps.execute(sql);  //添加
        } catch (Exception e) {
            System.out.println(e.toString());
            return false;
        }
    }








}

\src\com\user\dao\UserDao.java

package com.user.dao;

import com.data.entity.User;

import java.util.List;

public interface UserDao {

    /**
     * 添加用户
     * @param user 用户类
     * @return 影响行
     */
    public int insertUser(User user);

    /**
     * 删除数据
     * @param id 删除ID
     * @return 影响行
     */
    public int deleteUser(int id);

    /**
     * 修改数据
     * @param user 用户类
     * @return 影响行
     */
    public int updateUserId(User user);

    /**
     * 查询用户
     * @param id ID
     * @return 用户数组
     */
    public List<User> selectUserId(int id);

    /**
     * 查询所有
     * @return
     */
    public List<User> selectUser();
}

\src\com\user\dao\impl\Userimpl.java

package com.user.dao.impl;


import com.data.entity.JDBCUtils;
import com.data.entity.Mysql_Template_conn;
import com.data.entity.User;
import com.user.dao.UserDao;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class Userimpl implements UserDao {
    @Override
    public int insertUser(User user) {
            String  insertDataxx = "INSERT into user(user_name,password) values(?,?)";
            return JDBCUtils.newJdbcTemplate().update(insertDataxx,user.getUserName(),user.getPassword());    //动态参数
    }

    @Override
    public int deleteUser(int id) {
            String  insertDataxx = "DELETE FROM user WHERE user_id=?";
            return JDBCUtils.newJdbcTemplate().update(insertDataxx,id);    //动态参数

    }

    @Override
    public int updateUserId(User user) {
            String  insertDataxx = "update user set user_name=?,password=?  where user_id=?";
            return JDBCUtils.newJdbcTemplate().update(insertDataxx,user.getUserName(),user.getPassword(),user.getUserId());    //动态参数
    }

    @Override
    public List<User> selectUserId(int id) {
            return JDBCUtils.newJdbcTemplate().query("SELECT * FROM user where user_id=?", new BeanPropertyRowMapper<>(User.class),id);
    }

    @Override
    public List<User> selectUser() {
        return JDBCUtils.newJdbcTemplate().query("SELECT * FROM user", new BeanPropertyRowMapper<>(User.class));
    }
}

\src\text11.java

import com.data.entity.User;
import com.user.dao.UserDao;
import com.user.dao.impl.Userimpl;

public class text11 {
    public static void main(String[] args) {
        UserDao add=new Userimpl();
        System.out.println(add.insertUser(new User(null,"abc","123465")));

        System.out.println(add.deleteUser(2));

        add.updateUserId(new User(1,"abc111","111111"));

        add.selectUserId(1).forEach(ss->{
            System.out.println(ss);
        });

        add.selectUser().forEach(ff->{
            System.out.println(ff);
        });

    }
}

java JDBCUtils 使用静态代码块连接数据库 全程值保持一次连接

java JDBCUtils 使用静态代码块连接数据库全程值保持一次连接