packagecom.txw.jdbc;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;@SuppressWarnings("all")// 注解警告信息public class JdbcTest01 {publicstaticvoidmain(String[]args)throwsException{// 1.加载驱动Class.forName("com.mysql.cj.jdbc.Driver");// 2 创建和数据库之间的连接Stringusername="testdb";Stringpassword="testxxxxxx";Stringurl="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai";Connectionconn=DriverManager.getConnection(url,username,password);// 3.准备发送SQLStringsql="select * from t_person";PreparedStatementpstm=conn.prepareStatement(sql);// 4.执行SQL,接收结果集ResultSetrs=pstm.executeQuery();// 5 处理结果集while(rs.next()){intpersonId1=rs.getInt("person_id");StringpersonName1=rs.getString("person_name");intage1=rs.getInt("age");Stringsex1=rs.getString("sex");Stringmobile1=rs.getString("mobile");Stringaddress1=rs.getString("address");System.out.println("personId="+personId1+",personName="+personName1+",age="+age1+",sex="+sex1+",mobile="+mobile1+",address="+address1);}// 6.释放资源rs.close();pstm.close();conn.close();}}
(1)Statement
每次执行时都需要编译,增大系统开销
1
2
3
4
5
6
7
//注入Stringsql="select * from user where username = '"+username+"'";Statementst=con.createStatement();ResultSetrs=st.executeQuery(sql);//预编译//无预编译,使用PreparedStatement代替Statement
(2)PrepareStatement
会对 SQL 语句进行预编译,减小系统开销
1
2
3
4
5
6
7
8
9
10
//注入Stringsql="select * from user where username = '"+username+"'";PreparedStatementps=con.prepareStatement(sql);ResultSetrs=ps.executeQuery();//预编译Stringsql="select * from user where username = ?";PreparedStatementps=con.prepareStatement(sql);ps.setString(1,username);ResultSetrs=ps.executeQuery();
//注入@RestControllerpublicclassJdbcController{@AutowiredprivateJdbcTemplatejdbcTemplate;@GetMapping("/users")publicList<Map<String,Object>>getUsers(Stringname){Stringsql="SELECT * FROM user WHERE username = '"+name+"'";List<Map<String,Object>>userList=jdbcTemplate.queryForList(sql);returnuserList;}}//预编译@RestControllerpublicclassJdbcController{@AutowiredprivateJdbcTemplatejdbcTemplate;@GetMapping("/users")publicList<Map<String,Object>>getUsers(Stringname){Stringsql="SELECT * FROM user WHERE username = ?";Object[]obj=newObject[]{name};List<Map<String,Object>>userList=jdbcTemplate.queryForList(sql,obj);returnuserList;}}
使用List最后转成Array
1
2
3
4
5
6
7
8
9
10
11
12
13
14
@RestControllerpublicclassJdbcController{@AutowiredprivateJdbcTemplatejdbcTemplate;@GetMapping("/users")publicList<Map<String,Object>>getUsers(Stringname){Stringsql="SELECT * FROM user WHERE username = ?";List<Object>params=newArrayList<Object>();params.add(name);List<Map<String,Object>>userList=jdbcTemplate.queryForList(sql,params.toArray());returnuserList;}}
//注入@RestControllerpublicclassUserController{@AutowiredprivateNamedParameterJdbcTemplatenamedParameterJdbcTemplate;@GetMapping("/test")publicList<String>getTest(@RequestParamStringid){Stringsql="SELECT * FROM user WHERE id = '"+id+"'";List<String>users=namedParameterJdbcTemplate.query(sql,newRowMapper<String>(){@OverridepublicStringmapRow(ResultSetrs,introwNum)throwsSQLException{returnrs.getInt("id")+rs.getString("username");}});returnusers;}}//预编译@RestControllerpublicclassUserController{@AutowiredprivateNamedParameterJdbcTemplatenamedParameterJdbcTemplate;@GetMapping("/test")publicList<String>getTest(@RequestParamStringid){Stringsql="SELECT * FROM user WHERE id = :id";Map<String,Object>params=newHashMap<>();params.put("id",id);List<String>users=namedParameterJdbcTemplate.query(sql,params,newRowMapper<String>(){@OverridepublicStringmapRow(ResultSetrs,introwNum)throwsSQLException{returnrs.getInt("id")+rs.getString("username");}});returnusers;}}
@RestControllerpublicclassUserController{@AutowiredprivateNamedParameterJdbcTemplatenamedParameterJdbcTemplate;@GetMapping("/test")publicList<String>getTest(@RequestParamLongid){Stringsql="SELECT * FROM user u WHERE u.id = :id";ParamBeanbean=newParamBean();bean.setId(id);SqlParameterSourceparam=newBeanPropertySqlParameterSource(bean);List<String>users=namedParameterJdbcTemplate.query(sql,param,newRowMapper<String>(){@OverridepublicStringmapRow(ResultSetrs,introwNum)throwsSQLException{returnrs.getInt("id")+rs.getString("username");}});returnusers;}}
@RepositorypublicclassUserDao{@AutowiredprivateEntityManagerentityManager;publicUserfindById(Longid){Stringhql="from User where id = :id";TypedQuery<User>query=entityManager.createQuery(hql,User.class);query.setParameter("id",id);returnquery.getSingleResult();}publicList<User>findAll(){Stringhql="from User";TypedQuery<User>query=entityManager.createQuery(hql,User.class);returnquery.getResultList();}publicPage<User>findPage(intpageNo,intpageSize,StringsortField,booleanascending){Stringhql="select u from User u order by u."+sortField+(ascending?" asc":" desc");TypedQuery<User>query=entityManager.createQuery(hql,User.class);query.setFirstResult((pageNo-1)*pageSize);query.setMaxResults(pageSize);List<User>resultList=query.getResultList();longtotalCount=count();returnnewPageImpl<>(resultList,PageRequest.of(pageNo-1,pageSize),totalCount);}publiclongcount(){Stringhql="select count(*) from User";TypedQuery<Long>query=entityManager.createQuery(hql,Long.class);returnquery.getSingleResult();}}
@AutowiredprivateEntityManagerentityManager;publicUserfindById(Stringid){Stringhql="from User where id = "+id;//User表名TypedQuery<User>query=entityManager.createQuery(hql,User.class);returnquery.getSingleResult();}
(2)createNativeQuery
是SQL查询方法
1
2
3
4
5
6
7
8
@AutowiredprivateEntityManagerentityManager;publicObjectfindById(Stringid){Stringhql="select * from user where id = "+id;//User表名Queryquery=entityManager.createNativeQuery(hql,User.class);returnquery.getSingleResult();}
Spring Data JPA不是一个完整JPA规范的实现,它只是一个代码抽象层,主要用于减少为各种持久层存储实现数据访问层所需的代码量。其底层依旧是Hibernate。可以把Spring Data JPA理解为JPA规范的再次封装抽象。JAVA业务层调用SpringData JPA二次封装提供的Repository层接口,进而基于JPA标准API进行处理,基于Hibernate提供的JPA具体实现,接着基于JDBC标准API接口,完成与实际DB之间的请求交互
名称
说明
JPA(Java Persistence API)
JPA是规范,它指明了持久化、读取和管理 Java 对象映射到数据库表时的规范。
JPQL(Java Persistence Query Language)
Spring Data JPA
是 Spring Framework 的一部分。它不是 JPA 的实现,而是在 JPA 之上提供更高层次的抽象,可以减少很多模板代码。而 Spring Data JAP 的默认实现是 Hibernate,当然也可以其他的 JPA Provider。
publicinterfaceUserRepositoryextendsJpaRepository<User,Long>{UserfindByUsername(Stringusername);// JPA根据方法名自动生成查询语句 /org/springframework/aop/framework/JdkDynamicAopProxy.class//原生SQL//@Query(value = "select * from user where username = ?1", nativeQuery = true)//User fuzzyQueryByName(String username);//HQL提供的查询语法//@Query("FROM User u WHERE u.username = :username")//User fuzzyQueryByName(@Param("username") String username);//JPQL1提供的查询语法@Query("SELECT u FROM User u WHERE u.username = :username")UserfuzzyQueryByName(@Param("username")Stringusername);//JPQL2提供的查询语法//@Query("SELECT u FROM User u WHERE u.username = ?1")//User fuzzyQueryByName(String username);}
JpaRepository 是 Spring Data JPA 提供的一个接口,它是对 EntityManager 的封装,并提供了一些额外的功能,比如自动生成常见的 CRUD 操作,以及通过方法名来定义查询等。JpaRepository 还支持分页、排序、锁定等高级功能,使得对数据库的访问更加方便。
1
2
3
//编写符合规范,不存在注入问题@Query(value="SELECT * FROM user WHERE username = 'exampleuser' ORDER BY :orderby",nativeQuery=true)UserfuzzyQueryByName(@Param("orderby")Stringorderby);
@MapperpublicinterfaceUserMapper{@Select("SELECT id, username, password FROM user WHERE id = #{id}")UserfindById(Stringid);@Select("SELECT id, username, password FROM user")List<User>findAll();}
@MapperpublicinterfaceUserMapper{// 不存在注入@Select("SELECT id, username, password FROM user WHERE id = #{id}")UserfindById(Stringid);// 存在注入@Select("SELECT id, username, password FROM user WHERE id = '${id}'")UserfindById(Stringid);}
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.example.mybatis.entity.UserMapper"><!-- sql注入--><selectid="findById"resultType="com.example.mybatis.User"> SELECT * FROM user WHERE id = ${id}
</select><!-- 预编译--><selectid="findById"resultType="com.example.mybatis.User"> SELECT * FROM user WHERE id = #{id}
</select></mapper>
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.example.mybatis.mapper.UserMapper"><selectid="selectNameById"resultType="com.example.mybatis.entity.User"> select * from user where id = ${id}
</select><!--预编译--><selectid="selectNameById"resultType="com.example.mybatis.entity.User"> select * from user where id = #{id}
</select></mapper>