SPINGBOOT 第五章

gcaling2006

已于 2023-05-14 16:41:33 修改

阅读量85

点赞数

文章标签： mysql java 数据库

于 2023-05-14 08:32:55 首次发布

本文链接：https://blog.csdn.net/gcaling2006/article/details/130665157

版权

第一节：

jdbctemplate 使用AOP思想

mysql版本号要注意，插入时可以获取ID

public int addUser2(User user) {
GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
int result = jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement("insert into user (username,address) values(?,?)", Statement.RETURN_GENERATED_KEYS);
ps.setString(1, user.getUsername());
ps.setString(2, user.getAddress());
return ps;
}
}, keyHolder);
user.setId(keyHolder.getKey().longValue());
return result;
}
返回所有用户

public List<User> getAllUsers() {
List<User> list = jdbcTemplate.query("select * from user", new RowMapper<User>() {
@Override
public User mapRow(ResultSet resultSet, int i) throws SQLException {
String username = resultSet.getString("username");
String address = resultSet.getString("address");
long id = resultSet.getLong("id");
User user = new User();
user.setId(id);
user.setUsername(username);
user.setAddress(address);
return user;
}
});
return list;
}

这个方法要简单的多，查询出来的列和USER对象属性要一一对应就可以
public List<User> getAllUsers2() {
List<User> list = jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
return list;
}

JDBCTemplate多数据源

@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.one")
DataSource dsOne() {
return new HikariDataSource();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.two")
DataSource dsTwo() {
return new HikariDataSource();
}
}

@Configuration
public class JdbcTemplateConfig {

@Bean
JdbcTemplate jdbcTemplateOne(@Qualifier("dsOne") DataSource ds) {
return new JdbcTemplate(ds);
}

@Bean
JdbcTemplate jdbcTemplateTwo(@Qualifier("dsTwo") DataSource ds) {
return new JdbcTemplate(ds);
}
}

@SpringBootTest
class JdbctemplatemultiApplicationTests {

@Autowired
@Qualifier("jdbcTemplateOne")
JdbcTemplate jdbcTemplateOne;

@Resource(name = "jdbcTemplateTwo")
JdbcTemplate jdbcTemplateTwo;
@Test
void contextLoads() {
List<User> list1 = jdbcTemplateOne.query("select * from user", new BeanPropertyRowMapper<>(User.class));
List<User> list2 = jdbcTemplateTwo.query("select * from user", new BeanPropertyRowMapper<>(User.class));
System.out.println("list1 = " + list1);
System.out.println("list2 = " + list2);
}

}
第二节

整合mybatis

@MapperScan()加到启动类上面

public interface

@select("select * from user where id=#{d}")

User getUserbyId(Long id);

@Resuls({

@Result(property="address",column="address1")

})

@select("select * from user")

List<user> getAllUsers();

@Insert("insert into user (username,address) values (#{username},#{address})

@SelectKey(statement="select last_insert_id()",keyproperty="id",before=false,resultType=Long.class)

@Delete("delete from user wehre id=#{id}")

Integer deletebyId(Long id)

@Update("update user set username=#{username} wehre id=#{id}")

Integer updateById(String username,long id)

XML文件实现mybatis

<select id="getUserById" resultMap="UserMap">
select * from user where id=#{id};
</select>

<select id="getAllUsers" resultMap="UserMap">
select * from user ;
</select>

<insert id="addUser" parameterType="org.javaboy.mybatis.model.User" useGeneratedKeys="true" keyProperty="id">
insert into user (username,address1) values (#{username},#{address});
</insert>

<delete id="deleteById">
delete from user where id=#{id}
</delete>
<update id="updateById">
update user set username = #{username} where id=#{id};
</update>
xml文件的位置放在mapper里面，要改一下POM文件

<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
</resource>
</resources>

mybatis.mapper-location=classpath:mapper/*.xml

mybatis多数据源

@Configuration
@MapperScan(basePackages = "org.javaboy.mybatismulti.mapper1",sqlSessionFactoryRef = "sqlSessionFactory1",sqlSessionTemplateRef = "sqlSessionTemplate1")
public class MyBatisConfigOne {
@Autowired
@Qualifier("dsOne")
DataSource ds;

@Bean
SqlSessionFactory sqlSessionFactory1() {
SqlSessionFactory sqlSessionFactory = null;
try {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(ds);
sqlSessionFactory = bean.getObject();
} catch (Exception e) {
e.printStackTrace();
}
return sqlSessionFactory;
}

@Bean
SqlSessionTemplate sqlSessionTemplate1() {
return new SqlSessionTemplate(sqlSessionFactory1());
}

@Configuration
@MapperScan(basePackages = "org.javaboy.mybatismulti.mapper2",sqlSessionFactoryRef = "sqlSessionFactory2",sqlSessionTemplateRef = "sqlSessionTemplate2")
public class MyBatisConfigTwo {
@Autowired
@Qualifier("dsTwo")
DataSource ds;

@Bean
SqlSessionFactory sqlSessionFactory2() {
SqlSessionFactory sqlSessionFactory = null;
try {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(ds);
sqlSessionFactory = bean.getObject();
} catch (Exception e) {
e.printStackTrace();
}
return sqlSessionFactory;
}

@Bean
SqlSessionTemplate sqlSessionTemplate2() {
return new SqlSessionTemplate(sqlSessionFactory2());
}
第三节

mysql主从复制

springboot动态数据源

网上工具 <dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.3.1</version>
</dependency>

spring:
datasource:
dynamic:
primary: master #设置默认的数据源或者数据源组,默认值即为master
strict: false #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候会抛出异常,不启动则使用默认数据源.
datasource:
master:
url: jdbc:mysql://localhost:33061/javaboydb
username: root
password: 123
slave:
url: jdbc:mysql://localhost:33062/javaboydb
username: root
password: 123

@Service
public class UserService {
@Autowired
UserMapper userMapper;

@DS("master")
public Integer addUser(User user) {
return userMapper.addUser(user);
}

@DS("slave")
public List<User> getAllUsers() {
return userMapper.getAllUsers();
}
}

第四节

JPA JAVA持久化的API

JPA是Hibernate功能的一个子集，JPA规范，提供了一些编程的API接口。

springdata 支持mongodb neo4j redis hbase

@Entity(name = "t_book")
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "b_name")
private String name;
private String author;

跟据对象自动把表创建出来

@Autowired
BookDao bookDao;

@Test
void contextLoads() {
Book book = new Book();
book.setName("三国演义");
book.setAuthor("罗贯中");
bookDao.save(book);
}

@Test
void test1() {
List<Book> list = bookDao.findAll();
System.out.println("list = " + list);
Optional<Book> byId = bookDao.findById(2L);
System.out.println("byId = " + byId);
bookDao.deleteById(1L);
}
@Test
void test2() {
//页码从 0 开始记，1 表示第二页
PageRequest pageRequest = PageRequest.of(0, 3, Sort.by(Sort.Order.asc("id")));
Page<Book> page = bookDao.findAll(pageRequest);
System.out.println("总记录数： " + page.getTotalElements());
System.out.println("总页数 " + page.getTotalPages());
System.out.println("查到的数据 " + page.getContent());
System.out.println("每页的记录数 " + page.getSize());
System.out.println("是否还有下一页 " + page.hasNext());
System.out.println("是否还有上一页 " + page.hasPrevious());
System.out.println("是否最后一页 " + page.isLast());
System.out.println("是否第一页 " + page.isFirst());
System.out.println("当前页码 " + page.getNumber());
System.out.println("当前页的记录数 " + page.getNumberOfElements());
}
只要方法名称满足规则，就可以不用写SQL语句。

SPING JPA 自定义查询

List<Book> getBookByAuthorIs(String author);

@Query(nativeQuery = true,value = "select * from t_book where id=(select max(id) from t_book)")
Book maxIdBook();

@Query("update t_book set b_name=:name where id=:id")
@Modifying
void updateBookById(String name, Long id);
}

@Service
public class BookService {
@Autowired
BookDao bookDao;
@Transactional
public void updateBookById(String name, Long id){
bookDao.updateBookById(name, id);
}
}

JPA多数据源

spring.datasource.one.username=root
spring.datasource.one.password=123
spring.datasource.one.jdbcUrl=jdbc:mysql:///test01

spring.datasource.two.username=root
spring.datasource.two.password=123
spring.datasource.two.jdbcUrl=jdbc:mysql:///test02

spring.jpa.database=mysql
spring.jpa.database-platform=mysql
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect

@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.one")
@Primary
DataSource dsOne() {
return new HikariDataSource();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.two")
DataSource dsTwo() {
return new HikariDataSource();
}
}

@Configuration
@EnableJpaRepositories(basePackages = "org.javaboy.jpamulti.dao1", entityManagerFactoryRef = "localContainerEntityManagerFactoryBean1", transactionManagerRef = "platformTransactionManager1")
public class JpaConfigOne {
@Autowired
@Qualifier("dsOne")
DataSource ds;
@Autowired
JpaProperties jpaProperties;

@Bean
@Primary
LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean1(EntityManagerFactoryBuilder builder) {
return builder.dataSource(ds)
.packages("org.javaboy.jpamulti.model")
.properties(jpaProperties.getProperties())
.persistenceUnit("pu1")
.build();
}

@Bean
PlatformTransactionManager platformTransactionManager1(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(localContainerEntityManagerFactoryBean1(builder).getObject());
}

@Configuration
@EnableJpaRepositories(basePackages = "org.javaboy.jpamulti.dao2", entityManagerFactoryRef = "localContainerEntityManagerFactoryBean2", transactionManagerRef = "platformTransactionManager2")
public class JpaConfigTwo {
@Autowired
@Qualifier("dsTwo")
DataSource ds;
@Autowired
JpaProperties jpaProperties;

@Bean
LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean2(EntityManagerFactoryBuilder builder) {
return builder.dataSource(ds)
.packages("org.javaboy.jpamulti.model")
.properties(jpaProperties.getProperties())
.persistenceUnit("pu2")
.build();
}

@Bean
PlatformTransactionManager platformTransactionManager2(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(localContainerEntityManagerFactoryBean2(builder).getObject());
}