//第一个dependency,是添加的mysql链接java的驱动程序
//第二个dependency,是支持通过JDBC链接数据库。和C#的ADO一样，都是基于ODBC链接的。
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
###mysql 连接信息,test是数据库名
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test
###用户名
spring.datasource.username=root
###密码
spring.datasource.password=sa
###驱动
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
CREATE TABLE `ay_user` (
`id` varchar(32) NOT NULL,
`name` varchar(10) DEFAULT NULL COMMENT ‘用户名‘,
`password` varchar(32) DEFAULT NULL COMMENT ‘密码‘
) ENGINE=InnoDB DEFAULT CHARSET=utf8
public class AyUser {
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
private String id;
private String name;
private String password;
}
@Resource
private JdbcTemplate jdbcTemplate;
@Test
public void mySqlTest(){
String sql = "select id,name,password from ay_user ";
//query(),查询方法，传入sql语句和RowMapper对象，返回对象List。
//RowMapper对象，将查询出来的每一行数据封装成用户定义的类
List<AyUser> userList = (List<AyUser>) jdbcTemplate.query(sql, new RowMapper<AyUser>() {
@Override
public AyUser mapRow(ResultSet re, int rowNum) throws SQLException {
AyUser user = new AyUser();
user.setId(re.getString("id"));
user.setName(re.getString("name"));
user.setPassword(re.getString("password"));
return user;
}
});
System.out.println("查询成功：");
for (AyUser user:userList){
System.out.println("[id]："+user.getId()+";[name]:"+user.getName());
}
}
@Resource
private JdbcTemplate jdbcTemplate;
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
public interface AyUserRepository extends JpaRepository<AyUser,String> {
//自定义内容
List<AyUser> findByName(String name);
List<AyUser> findByNameLike(String name);
List<AyUser> findByIdIn(Collection<String> ids);
}
@Entity//每个持久化POJO类都是一个实体Bean，通过此注解申明
@Table(name="ay_user")//对象映射到数据库的表，如果没有，Spring会根据class的名字进行寻找
public class AyUser {
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Id//表示表的主键
private String id;
private String name;
private String password;
}
public interface AyUserService {
AyUser findById(String id);
List<AyUser> findAll();
AyUser save(AyUser ayUser);
void delete(String id);
//Pageable是一个分页接口，Page是一个分页查询结果借口
Page<AyUser> findAll(Pageable pageable);
List<AyUser> findByName(String name);
List<AyUser> findByNameLike(String name);
List<AyUser> findByIdIn(Collection<String> ids);
}
@Service//服务类注解，和@Component起到类似的作用：自动扫描并注册到Spring容器中
public class AyUserServiceImpl implements AyUserService {
@Resource//自动扫描AyUserRepository并注册
private AyUserRepository ayUserRepository;
@Override
public AyUser findById(String id) {
return ayUserRepository.findById(id).get();
}
@Override
public List<AyUser> findAll() {
return ayUserRepository.findAll();
}
@Override
public AyUser save(AyUser ayUser) {
return ayUserRepository.save(ayUser);
}
@Override
public void delete(String id) {
ayUserRepository.deleteById(id);
}
//翻页
@Override
public Page<AyUser> findAll(Pageable pageable) {
return ayUserRepository.findAll(pageable);
}
@Override
public List<AyUser> findByName(String name) {
return ayUserRepository.findByName(name);
}
@Override
public List<AyUser> findByNameLike(String name) {
return ayUserRepository.findByNameLike(name);
}
@Override
public List<AyUser> findByIdIn(Collection<String> ids) {
return ayUserRepository.findByIdIn(ids);
}
}
@Resource
private AyUserService ayUserService;
@Test
public void testRepository(){
List<AyUser> userList = ayUserService.findAll();
System.out.println("findAll():"+userList.size());
List<AyUser> userList2 = ayUserService.findByName("文鹏");
System.out.println("findByName():"+userList2.size());
Assert.isTrue(userList2.get(0).getName().equals(("文鹏")));
List<AyUser> userList3 = ayUserService.findByNameLike("文%");
System.out.println("findByNameLike():"+userList3.size());
Assert.isTrue(userList3.get(0).getName().equals(("文鹏")));
List<String> ids = new ArrayList<String>();
ids.add("1");
ids.add("2");
List<AyUser> userList4 = ayUserService.findByIdIn(ids);
System.out.println("findByIdIn:"+userList4.size());
PageRequest pageRequest = PageRequest.of(0,10);
Page<AyUser> userList5 = ayUserService.findAll(pageRequest);
System.out.println("page findAll:"+userList5.getTotalPages()+"/"+userList5.getSize());
List<AyUser> userList6 = ayUserService.findByNameLike("%国%");
System.out.println("findByNameLike():"+userList6.size());
Assert.isTrue(userList6.get(0).getName().equals(("文国平")));
AyUser ayUser = new AyUser();
ayUser.setId("4");
ayUser.setName("李国正");
ayUser.setPassword("123");
ayUserService.save(ayUser);
List<AyUser> userList7 = ayUserService.findByNameLike("%国%");
System.out.println("findByNameLike():"+userList7.size());
Assert.isTrue(userList7.get(0).getName().equals(("文国平")));
ayUserService.delete("4");
List<AyUser> userList8 = ayUserService.findByNameLike("%国%");
System.out.println("findByNameLike():"+userList8.size());
Assert.isTrue(userList8.get(0).getName().equals(("文国平")));
}
@Transactional
@Service
public class AyUserServiceImpl implements AyUserService {
@Transactional
@Override
public void delete(String id) {
ayUserRepository.deleteById(id);
String e = null;
e.split("/");
}
}
@Transactional(propagation=Propagation.REQUIRED) //控制事务传播。默认是Propagation.REQUIRED
@Transactional(isolation=Isolation.DEFAULT) //控制事务隔离级别。默认跟数据库的默认隔离级别相同
@Transactional(readOnly=false) //控制事务可读写还是只可读。默认可读写
@Transactional(timeout=30) //控制事务的超时时间，单位秒。默认跟数据库的事务控制系统相同，又说是30秒
@Transactional(rollbackFor=RuntimeException.class) //控制事务遇到哪些异常才会回滚。默认是RuntimeException
@Transactional(rollbackForClassName=RuntimeException) //同上
@Transactional(noRollbackFor=NullPointerException.class) //控制事务遇到哪些异常不会回滚。默认遇到非RuntimeException不会回滚
@Transactional(noRollbackForClassName=NullPointerException)//同上
原文：https://www.cnblogs.com/wenpeng/p/12045377.html