org.springframework.boot
spring-boot-starter-jdbc
mysql
mysql-connector-java
8.0.11
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://localhost:3306/jdbctest?createDatabaseIfNotExist=true&useSSL=false&serverTimezone=GMT%2b8&characterEncoding=utf8&connectTimeout=1000&socketTimeout=15000&autoReconnect=true
#MySQL5.7以上的版本使用com.mysql.cj.jdbc.Driver
#MySQL5.7以下的版本使用com.mysql.jdbc.Driver
driver-class-name: com.mysql.cj.jdbc.Driver
自动装配Bean对象
@Autowired
JdbcTemplate jdbcTemplate;
jdbcTemplate通过execute方法执行DDL语句
String sql = "create table sys_user(id int AUTO_INCREMENT,username varchar(255),password varchar(255),age int,PRIMARY KEY (id))ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_0900_ai_ci;";
jdbcTemplate.execute(sql);
System.out.println("创建表成功");
表对应的实体类为
public class SysUser implements Serializable {
public SysUser(){}
public SysUser(Integer id, String username, String password, Integer age){
this.id=id;
this.username=username;
this.password=password;
this.age=age;
}
private Integer id;
private String username;
private String password;
private Integer age;
//省略 get,set方法
@Override
public String toString() {
return "SysUsers{" +
"id=" + id +
"username='" + username + '\'' +
", password='" + password + '\'' +
", age='" + age + '\'' +
'}';
}
}
//1.插入数据 占位符
String sql = "insert into sys_user (username,password,age) values(?,?,?);";
int ret = jdbcTemplate.update(sql,"王五","123456",18);
if (ret == 0) {
System.out.println("添加失败!");
}
//插入测试数据方便后面分页测试
sql = "insert into sys_user (username,password,age) values('张三','李四',18);";
for (int i = 0; i < 20; i++) {
ret = jdbcTemplate.update(sql);
if (ret == 0) {
System.out.println("添加失败!");
}
}
//2.插入数据并返回主键
KeyHolder keyHolder1 = new GeneratedKeyHolder();
String finalSql = sql;
ret = jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
// 设置返回的主键字段名
PreparedStatement ps = con.prepareStatement(finalSql, Statement.RETURN_GENERATED_KEYS);
return ps;
}
}, keyHolder1);
if (ret == 1) {
// 获取到插入数据生成的自增ID
int id = keyHolder1.getKey().intValue();
System.out.println(id);
}
String sql = "select * from sys_user";
//1.使用map封装查询出来的字段名称和字段值
System.out.println("返回map数据");
List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
for (Map<String, Object> map : mapList) {
for (Map.Entry<String, Object> entry : map.entrySet()) {
System.out.println("key=" + entry.getKey() + ",value=" + entry.getValue());
}
}
//2.通过RowMapper获取ResultSet手动赋值给bean
System.out.println("手动注入");
List<SysUser> sysUserList = jdbcTemplate.query(sql, new RowMapper<SysUser>() {
@Override
public SysUser mapRow(ResultSet resultSet, int i) throws SQLException {
SysUser sysUser = new SysUser(resultSet.getInt("id"), resultSet.getString("username"), resultSet.getString("password"), resultSet.getInt("age"));
return sysUser;
}
});
for (SysUser sysUser : sysUserList) {
System.out.println(sysUser.toString());
}
//3.通过BeanPropertyRowMapper 克隆字段
System.out.println("BeanPropertyRowMapper 克隆");
sysUserList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SysUser.class));
for (SysUser sysUser : sysUserList) {
System.out.println(sysUser.toString());
}
//4.分页查询
Integer totalElements = jdbcTemplate.queryForObject("SELECT count(id) FROM sys_user", Integer.class);
//当前页
Integer currentPage = 1;
//页码大小
Integer pageSize = 5;
sysUserList = jdbcTemplate.query("SELECT * FROM sys_user limit ?,?", new BeanPropertyRowMapper<>(SysUser.class), (currentPage - 1) * pageSize, pageSize);
for (SysUser sysUser : sysUserList) {
System.out.println(sysUser.toString());
}
System.out.println("总记录数=" + totalElements + ",总页数=" + (totalElements % pageSize == 0 ? totalElements / pageSize : totalElements / pageSize + 1));
也可像3.2一样使用占位符方式操作
String sql = "update sys_user set username='李四' where id=1";
int ret = jdbcTemplate.update(sql);
if (ret == 1) {
System.out.println("修改成功");
}
String sql = "delete from sys_user where id=?";
int ret = jdbcTemplate.update(sql, 1);
if (ret == 1) {
System.out.println("删除成功");
}
创作不易,要是觉得我写的对你有点帮助的话,麻烦在github上帮我点下 Star
【SpringBoot框架篇】其它文章如下,后续会继续更新。