//xml配置
private static final ApplicationContext context = new ClassPathXmlApplicationContext("config/db.xml");
//jdbc
private static final JdbcTemplate jdbcTemplate = context.getBean(JdbcTemplate.class);
@Test
public void test1() throws SQLException {
ApplicationContext context = new ClassPathXmlApplicationContext("config/db.xml");
HikariConfig config = (HikariConfig) context.getBean("dbConfig");
HikariDataSource dataSource = context.getBean(HikariDataSource.class);
JdbcTemplate jdbcTemplate = context.getBean(JdbcTemplate.class);
System.out.println(config);
System.out.println(dataSource);
System.out.println(jdbcTemplate);
//查询操作
String sql = "select id from szg8 where `name` = ?";
Integer id = jdbcTemplate.queryForObject(sql, int.class, "张三");
System.out.println(id);
}
//多条查询
@Test
public void test2() {
String sql = "select * from szg8 where `id` > ?";
RowMapper <Szg8> rowMapper = new BeanPropertyRowMapper <>(Szg8.class);
List <Szg8> szg8s = jdbcTemplate.query(sql, rowMapper, 1);
System.out.println(szg8s);
}
//增
@Test
public void add() {
String sql = "insert INTO szg8 (name) values (?) ";
int update = jdbcTemplate.update(sql, "陈六");
System.out.println(update);
}
//循环增加
@Test
public void forAdd() {
LocalDateTime startTime = LocalDateTime.now();
List <Object[]> args = new ArrayList <>(1000);
String sql = "insert INTO szg8 (name) values (?) ";
for (int i = 0; i < Short.MAX_VALUE; i++) {
args.add(new Object[]{i});
}
// for (int i = 0; i < 1000; i++) {
// add();
// }
int[] ints = jdbcTemplate.batchUpdate(sql, args);
System.out.println(ints.length);
LocalDateTime endTime = LocalDateTime.now();
System.out.println(Duration.between(startTime, endTime).getSeconds());
}
//批量改动
@Test
public void batchUpdate() {
LocalDateTime startTime = LocalDateTime.now();
List <Object[]> args = new ArrayList <>(1000);
String sql = "update szg8 set `name`= '任杰' where `id` = ?";
for (int i = 1; i < Short.MAX_VALUE + 1; i++) {
args.add(new Object[]{i});
}
int[] ints = jdbcTemplate.batchUpdate(sql, args);
System.out.println(ints.length);
LocalDateTime endTime = LocalDateTime.now();
System.out.println(Duration.between(startTime, endTime).getSeconds());
}
//删除
@Test
public void delete() {
String sql = "delete from szg8 where `name` = ?";
int delete = jdbcTemplate.update(sql, "陈六");
System.out.println(delete);
}
//改
@Test
public void update() {
String sql = "update szg8 set `name`= ? where `id` = ?";
int update = jdbcTemplate.update(sql, "任杰", 2);
System.out.println(update);
}
//具名参数
@Test
public void test4() {
NamedParameterJdbcTemplate namedParameterJdbcTemplate = context.getBean(NamedParameterJdbcTemplate.class);
String sql = "select * from szg8 where id=:id";
Map <String, Object> paraMap = new HashMap <>();
paraMap.put("id", 23);
RowMapper <Szg8> rowMapper = new BeanPropertyRowMapper <>(Szg8.class);
List <Szg8> query = namedParameterJdbcTemplate.query(sql, paraMap, rowMapper);
System.out.println(query);
}
public static void main(String[] args) {
Properties properties = new Properties();
properties.setProperty("username", "root");
properties.setProperty("password", "root");
properties.setProperty("jdbcUrl", "jdbc:mysql://localhost:3306/szg8?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC");
properties.setProperty("driverClassName", "com.mysql.cj.jdbc.Driver");
HikariConfig hikariConfig = new HikariConfig(properties);
HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);
}
dbconfig.property文件内容
user=root
password=root
jdbcUrl=jdbc:mysql://localhost:3306/szg8?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&rewriteBatchedStatements=true&characterEncoding=utf-8
driverClassName=com.mysql.cj.jdbc.Driver
db.xml内容
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context" xmlns:P="http://www.springframework.org/schema/c"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<context:property-placeholder location="config/dbconfig.property"/>
<!-- hikar的config配置-->
<bean id="dbConfig" class="com.zaxxer.hikari.HikariConfig">
<property name="username" value="${user}"/>
<property name="password" value="${password}"/>
<property name="jdbcUrl" value="${jdbcUrl}"/>
<property name="driverClassName" value="${driverClassName}"/>
</bean>
<!-- 数据源-->
<bean id="db" class="com.zaxxer.hikari.HikariDataSource">
<constructor-arg name="configuration" ref="dbConfig"/>
</bean>
<!-- spring的jdbc构造-->
<bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg name="dataSource" ref="db"/>
</bean>
</beans>