开始之前你必须在项目配置好数据库，本文使用的spring boot，相比spring，spring boot省去了很多各种对以来组件复杂的配置，直接在pom配置组件，完后会自动帮我们导入组件
<!-- 导入SpringDataJPA的坐标 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- 连接数据库驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- 连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.9</version>
</dependency>
然后在src/main/resources下，新建application.properties文件，用配置连接数据库的参数
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test_db
spring.datasource.username=root
spring.datasource.password=123
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.http.multipart.maxFileSize=1024Mb
spring.http.multipart.maxRequestSize=1034Mb
另外的，由于jpa操作是模型驱动的，所以需要对操作的表定义模型
package cn.yitiantian.test.model;
import org.hibernate.annotations.DynamicInsert;
import org.hibernate.annotations.DynamicUpdate;
import javax.persistence.*;
@DynamicUpdate
@DynamicInsert
@Entity
@Table(name = "user")
public class user {
@Id
@GeneratedValue
@Column(name = "id")
Integer id;
@Column(name = "name")
String name;
@Column(name = "caption")
Integer captionId;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getCaptionId() {
return captionId;
}
public void setCaptionId(Integer captionId) {
this.captionId = captionId;
}
}
这样，我们就可以开始操作数据库了
基本查询
基本查询中分为两种，一种是spring data默认已经实现，一种根据查询的方法来自动解析成SQL
默认已经实现的方法是一些最基本的增删改查方法，如findAll()、delete(user)
而自定义简单查询，则是通过关键词和属性组合而成，然后jpa会自动解析成SQL语句执行,如findByCaptionId、findByNameOrderById
更多基本方法和关键词使用详见 jpa
自定义SQL查询
除了基本查询，jpa还是支持自定义SQL，只要使用@Query注解，如果涉及到动数据，还要加上Modifying，另外还支持事务操作
更多基本方法和关键词使用详见 jpa
public interface EmpRepository extends JpaRepository<Emp, Integer>,JpaSpecificationExecutor<Emp>{
//查询name(驼峰式名称)
public List<Emp> findByName(String name);
public List<Emp> findByNameAndGender(String name,String gender);
public List<Emp> findByTelephoneLike(String telphone);
@Query("from Emp where name = ?")
public List<Emp> queryName(String name);
@Query(value="select * from t_emp where name = ?",nativeQuery=true)
public List<Emp> queryName2(String name);
@Query("update Emp set address = ? where id = ?")
@Modifying // 进行修改操作
public void updateAddressById(String address,Integer id);
}
当然，如果你想在执行SQL语句前，就想定义好一个模型来接收查询的数据，你还可以这么做
第一步，先定义一个数据库操作的扩展类
public interface MergedVersionRepositoryExtend {
public List<MergedVersion> findVersionByTableId();
}
第二步，再让数据库操作类集成这个扩展类
public interface MergedVersionRepository extends MergedVersionRepositoryExtend {
}
第三步，定义获取数据方法，并封装到模型里
@Repository
public class MergedVersionRepositoryImpl implements MergedVersionRepositoryExtend {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public List<MergedVersion> findVersionByTableId() {
String sql = "SELECT DISTINCT
" +
" versionT.version AS version,
" +
" tableT.id AS id,
" +
" tableT.`name` AS name
" +
"FROM
" +
" u_showinfo_table AS tableT
" +
"LEFT OUTER JOIN u_showinfo_detail_table AS detailT ON detailT.table_id=tableT.id
" +
"LEFT OUTER JOIN u_showinfo_property AS propertyT ON propertyT.detail_table_id=detailT.id
" +
"LEFT OUTER JOIN u_showinfo_version AS versionT ON versionT.property_id=propertyT.id
" +
"ORDER BY
" +
" versionT.version";
List<MergedVersion> versionList = jdbcTemplate.query(sql, new MergedVersionMapper());
return versionList;
}
}
第四步，定义模型
public class MergedVersion {
private int tableId;
private String tableName;
private String version;
public int getTableId() {
return tableId;
}
public void setTableId(int tableId) {
this.tableId = tableId;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getVersion() {
return version;
}
public void setVersion(String version) {
this.version = version;
}
}
第五步，定义将查询数据注入模型里的方法
public class MergedVersionMapper implements RowMapper<MergedVersion> {
@Override
public MergedVersion mapRow(ResultSet rs, int rowNum) throws SQLException {
MergedVersion mergedVersion = new MergedVersion();
mergedVersion.setTableId(rs.getInt("id"));
mergedVersion.setTableName(rs.getString("name"));
mergedVersion.setVersion(rs.getString("version"));
return mergedVersion;
}
}
复杂查询
排序
@Override
public List<ShowInfoTable> findAllTable() {
Sort sort = new Sort(Sort.Direction.ASC, "id");
return showInfoTableRepository.findAll(sort);
}
除了在查询方法里实现排序，对结果集进行排序，可以这么做
class UserComparator implements Comparator {
@Override
public int compare(Object o1, Object o2) {
User prop1 = (User)o1;
User prop2 = (User)o2;
return prop1.getId().compareTo(prop2.getId());
}
}
调用(Collections.sort也可以直接支持列表排序)
UserComparator comparator = new UserComparator();
Collections.sort(users, comparator);
分页和限制查询
更多基本方法和关键词使用详见 jpa