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

\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);//方法2Mysql_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;  //PreparedStatementprivate ResultSet rs;public boolean link=false;  // staticpublic 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 {@Overridepublic int insertUser(User user) {String  insertDataxx = "INSERT into user(user_name,password) values(?,?)";return JDBCUtils.newJdbcTemplate().update(insertDataxx,user.getUserName(),user.getPassword());    //动态参数}@Overridepublic int deleteUser(int id) {String  insertDataxx = "DELETE FROM user WHERE user_id=?";return JDBCUtils.newJdbcTemplate().update(insertDataxx,id);    //动态参数}@Overridepublic 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());    //动态参数}@Overridepublic List<User> selectUserId(int id) {return JDBCUtils.newJdbcTemplate().query("SELECT * FROM user where user_id=?", new BeanPropertyRowMapper<>(User.class),id);}@Overridepublic 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);});}
}