1.spring jdbc
(1)pom.xml中导包
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.5.0</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.6.RELEASE</version>
</dependency>
(2)spring.xml配置数据源
bean 中name与id都是唯一标识某个bean,name的值可以是多个,id值只能是1个,
name和id可以同时出现,同时出现是id值和name值不能相同。
开发中通常使用name
<context:component-scan base-package="com.neuedu"/>
<bean name="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/shop"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
<!--初始连接数 -->
<property name="initialSize" value="10"></property>
<!--最大空闲连接 -->
<property name="maxIdle" value="20"></property>
<!-- 最大连接数 -->
<property name="maxTotal" value="100"></property>
<!--最小空闲连接 -->
<property name="minIdle" value="5"></property>
</bean>
<bean name="jdbctemp" class="org.springframework.jdbc.core.JdbcTemplate" p:dataSource-ref="dataSource">
(3)开发DAo层
//组件
@Repository
public class UserDaoImpl implements UserDao {
//注入JdbcTemplate
@Autowired
private JdbcTemplate jdbctem;
@Override
public boolean addUser(User user) {
String sql="insert into user(username,password) values(?,?)";
//update()增删改
Object arr[]={user.getUsername(),user.getPassword()};
int i=jdbctem.update(sql,arr);
if(i!=0){
return true;
}else{
return false;
}
}
@Override
public boolean updateUser(User user) {
String sql="update user set username=?,password=? where uid=?";
Object arr[]={user.getUsername(),user.getPassword(),user.getUid()};
int i=jdbctem.update(sql,arr);
return i!=0?true:false;
}
@Override
public boolean delUser(int id) {
String sql="delete from user where uid="+id;
int i=jdbctem.update(sql);
return i!=0?true:false;
}
@Override
public User getUserById(int id) {
String sql="select * from user where uid="+id;
//new BeanPropertyRowMapper<User>(User.class)
//处理结果集 将查询的结果中的列转换成java对象中的属性,并且创建Java对象
//User user=jdbctem.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class));
User uu=jdbctem.queryForObject(sql, new RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException{
User user=new User();
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setUid(rs.getInt("uid"));
return user;
}
});
return uu;
}
@Override
public List<User> getAllUser() {
String sql="select * from user";
List<User> list=jdbctem.query(sql, new RowMapper<User>(){
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User user=new User();
user.setUid(rs.getInt("uid"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
return user;
}
});
return list;
}
public List<Integer> getAllIds(){
String sql="select uid from user";
List<Integer> list=jdbctem.query(sql, new RowMapper<Integer>(){
@Override
public Integer mapRow(ResultSet rs, int arg1) throws SQLException {
return rs.getInt("uid");
}
});
return list;
}
}
(3)测试类
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:springConfig.xml"})
public class JunitTest {
@Autowired
private UserDao userDaoImpl;
@Test
public void t1(){
User user=new User();
user.setUsername("张三");
user.setPassword("123");
boolean b=userDaoImpl.addUser(user);
if(b){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
}
@Test
public void t2(){
User user=new User();
user.setUid(11);
user.setUsername("李四");
user.setPassword("456");
boolean b=userDaoImpl.updateUser(user);
if(b){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
}
@Test
public void t3(){
boolean b=userDaoImpl.delUser(11);
if(b){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
}
@Test
public void t4(){
//查询指定数据,必须保证数据存在,数据不存在就发生异常,设计时防止用户不验证,用于保证代码健壮性
//查询所有id
List<Integer> ids=userDaoImpl.getAllIds();
int id=12;
User user=null;
for (Integer i : ids) {
if(i==id){//存在
user=userDaoImpl.getUserById(id);
break;
}
}
if(user!=null){
System.out.println(user);
}else{
System.out.println("id值不存在");
}
}
@Test
public void t5(){
List<User> list=userDaoImpl.getAllUser();
System.out.println(list);
}
}
// 说明:增删改 jdbcTemplate中的update
(1)单个数据查询:必须验证数据存在性,保证数据存在,否则抛异常
queryForObject()
jdbctem.queryForObject(sql, new RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException{
User user=new User();
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setUid(rs.getInt("uid"));
return user;
}
});
处理结果集:用RowMapper接口的匿名对象,实现接口的row方法mapRow()
用子类BeanPropertyRowMapper() 要求传入对象的class字节码
jdbctem.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class))
(2)查询多条数据:query()
List<User> list=jdbctem.query(sql, new RowMapper<User>(){
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User user=new User();
user.setUid(rs.getInt("uid"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
return user;
}
});
2.spring事务管理
事务要么整体生效,要么整体失效。
在数据库上即多条SQL语句要么全执行成功,要么全执行失败。
数据库事务必须同时满足4个特性:
原子性(Atomic)
一致性(Consistency)
隔离性(Isolation)
持久性(Durabiliy)
ACID
一、xml
(1)pom.xml导包--aspectJ
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.1</version>
</dependency>
(2)spring.xml中,配置TX命名空间
xmlns:tx="http://www.springframework.org/schema/tx"
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd
(3)配置事务管理器
<tx:advice id="txAdvice" transaction-manager="txManager">
<tx:attributes>
<tx:method name="add*" propagation="REQUIRED"/>
<tx:method name="update*" propagation="REQUIRED"/>
</tx:attributes>
</tx:advice>
<!-- 切点表达式定义目标方法-->
<aop:config>
<aop:pointcut expression="execution (* com.neuedu.dao.UserDaoImpl.*(..))" id="serviceMethod"/>
<!-- 定义事务通知器(通知和切点)-->
<aop:advisor pointcut-ref="serviceMethod" advice-ref="txAdvice"/>
</aop:config>
(4) UserDaoImpl添加注解
//组件
@Repository
//事务
@Transactional
public class UserDaoImpl implements UserDao {
}
事务传播行为:
默认 REQURED
NOT_SUPPORTED不支持事务
二、注解
(1)配置事务管理器
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
p:dataSource-ref="ds"
/>
(2)打开事务驱动
<tx:annotation-driven transaction-manager="txManager"/>
(3) UserDaoImpl添加注解
//组件
@Repository
//事务
@Transactional
public class UserDaoImpl implements UserDao {
}
三、自动增长主键
public boolean addUser(User user) {
String s1="insert into user(username,password) values(?,?)";
String s2="insert into userDetail(uid,tel,addr,age) values(?,?,?,?)";
//user表
int uid=0;
Object obj=jdbctem.execute(new ConnectionCallback<Object>() {
@Override
public Object doInConnection(Connection con) throws SQLException, DataAccessException {
PreparedStatement pst=con.prepareStatement(s1,Statement.RETURN_GENERATED_KEYS); //想获取自动增长主键,必须传参 1
pst.setString(1, user.getUsername());
pst.setString(2, user.getPassword());
pst.executeUpdate();
Object obj=null;
//获取自动增长主键值
ResultSet rs=pst.getGeneratedKeys();
if(rs.next()){
obj=rs.getInt(1);
}
return obj;
}
});
uid=(Integer)obj;
//userDetail
Object arr2[]={uid,user.getTel(),user.getAddr(),user.getAge()};
int j=jdbctem.update(s2,arr2);
if(obj!=null&&j!=0){
return true;
}else{
return false;
}
}
//测试类
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:springConfig.xml"})
public class JunitTest {
@Autowired
private UserDao userDaoImpl;
@Test
public void t1(){
User user=new User();
user.setUsername("张三");
user.setPassword("123");
user.setTel("1389343554");
user.setAddr("哈尔滨");
boolean b=userDaoImpl.addUser(user);
if(b){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
}
}
4.简化配置文件
(1)pom.xml
<properties>
<project.build.sourceEncoding>utf-8</project.build.sourceEncoding>
<spring.version>5.0.6.RELEASE</spring.version>
</properties>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
(2)数据库配置文件与数据源
//jdbcConfig.properties
jdbc.driverName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/shop
jdbc.username=root
jdbc.password=root
jdbc.initialSize=10
jdbc.maxIdle=20
jdbc.minIdle=100
jdbc.minIdle=5
//springapp.xml
<context:property-placeholder location="classpath:jdbcConfig.properties"/>
<bean name="ds" class="org.apache.commons.dbcp2.BasicDataSource"
p:driverClassName="${jdbc.driverName}">
<!-- <property name="driverClassName" value="${jdbc.driverName}"></property> -->
<property name="url" value="${jdbc.url}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
<property name="initialSize" value="${jdbc.initialSize}"></property>
<property name="maxIdle" value="${jdbc.maxIdle}"></property>
<property name="maxTotal" value="${jdbc.minIdle}"></property>
<property name="minIdle" value="${jdbc.minIdle}"></property>
</bean>