java JDBC Mysql_Template 封装增删改查

package com.data.entity;

public class jobs {
    //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();

    public String jobId;
    public String jobTitle;  //job_title

    public jobs() {
    }

    public jobs(String jobId, String jobTitle) {
        this.jobId = jobId;
        this.jobTitle = jobTitle;
    }

    @Override
    public String toString() {
        return "jobs{" +
                "jobId='" + jobId + '\'' +
                ", jobTitle='" + jobTitle + '\'' +
                '}';
    }

    public String getJobId() {
        return jobId;
    }

    public void setJobId(String jobId) {
        this.jobId = jobId;
    }

    public String getJobTitle() {
        return jobTitle;
    }

    public void setJobTitle(String jobTitle) {
        this.jobTitle = jobTitle;
    }
}

package com.data.entity;

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

import javax.sql.DataSource;
import java.lang.invoke.TypeDescriptor;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/*
JDBC  Mysql_Template 封装增删改查
*QQ 7650371
* */
public class Mysql_Template_conn {
    public DataSource conn; // 声明Connection对象
    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_Template_conn(String HOSTNAME, String USERNAME, String PASSWORD, String DATABASE) {
        this.HOSTNAME = HOSTNAME;
        this.USERNAME = USERNAME;
        this.PASSWORD = PASSWORD;
        this.DATABASE = DATABASE;
    }

    public Mysql_Template_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_Template_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 DataSource getCon() {
        return conn;
    }

    public boolean getConnection() { // 建立连接
//        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.conn = new DriverManagerDataSource(jdbc, USERNAME, PASSWORD);
                System.out.println("数据库连接成功");
                this.link=true;
                return this.link;
            } catch (Exception e) {
                System.out.println(e.toString());
                this.link=false;
                return this.link;
            }
        } catch (ClassNotFoundException e) {
            System.out.println(e.toString());
            this.link=false;
            return this.link;
        }
    }

//public void executeQuery(String sql) {
//    try {
//        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.conn);
//        List<jobs> resultList = jdbcTemplate.query("SELECT * FROM jobs", new BeanPropertyRowMapper<>(jobs.class));
//        // 打印查询结果
//        System.out.println("查询结果为：");
//        resultList.forEach(person -> System.out.println(person));
//
//    } catch (Exception e) {
//        System.out.println(e.toString());
//    }
//}



    public int update(String sql,Object... args){   //动态参数  可以增删改，但是不能查询
        try {
            JdbcTemplate jdbcTemplate = new JdbcTemplate(this.conn);
            return jdbcTemplate.update(sql,args);  //添加
        } catch (Exception e) {
            System.out.println(e.toString());
            return 0;
        }
    }

    //


    public static void main(String[] args) {
        Mysql_Template_conn conn=new Mysql_Template_conn(
                "localhost","root","218484","data","3306","UTF-8");

        if(conn.getConnection()) {  // 建立连接
            try {
                JdbcTemplate jdbcTemplate = new JdbcTemplate(conn.conn);
                List<jobs> resultList = jdbcTemplate.query("SELECT * FROM jobs", new BeanPropertyRowMapper<>(jobs.class));
                // 打印查询结果
                System.out.println("查询结果为：");
                resultList.forEach(person -> System.out.println(person));
            } catch (Exception e) {
                System.out.println(e.toString());
            }

            try {  //动态参数
                JdbcTemplate jdbcTemplate = new JdbcTemplate(conn.conn);
                String id="xcc222";
                List<jobs> resultList = jdbcTemplate.query("SELECT * FROM jobs WHERE job_id=?", new BeanPropertyRowMapper<>(jobs.class),id);
                // 打印查询结果
                System.out.println("查询结果为：");
                resultList.forEach(person -> System.out.println(person));
            } catch (Exception e) {
                System.out.println(e.toString());
            }
        }
        String insertData = "INSERT into jobs(job_id,job_title) values('xcc222','22ffccfffffff')";
        System.out.println(conn.update(insertData));

        String UPDATEData = "UPDATE jobs set job_title='aaa' WHERE job_id='xcc222'";
        System.out.println(conn.update(UPDATEData));

        String insertDataxx = "INSERT into jobs(job_id,job_title) values(?,?)";
        String s1="xcc222xx";
        String s2="22ffccfffffffxx";
        System.out.println(conn.update(insertDataxx,s1,s2));    //动态参数


    }







}