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)); //动态参数
}
}