跟随小破站学习java spring框架第九天

讲解在spring中使用jdbc(非重点 但需理解) 公司中主要采用Orical

db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm
jdbc.username=root
jdbc.password=123456

jdbc.xml

<!-- 引入属性文件 -->
	<!-- <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="location" value="db.properties"></property>
	</bean> -->

	<!-- 引入属性文件 -->
	<context:property-placeholder location="db.properties"/>

	<!-- 创建数据源 -->
	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
		<property name="driverClassName" value="${jdbc.driver}"></property>
		<property name="url" value="${jdbc.url}"></property>
		<property name="username" value="${jdbc.username}"></property>
		<property name="password" value="${jdbc.password}"></property>
	</bean>

	<!-- 通过数据源配置JdbcTemplate -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>

public class Emp {

	private Integer eid;
	
	private String ename;
	
	private Integer age;
	
	private String sex;

	public Integer getEid() {
		return eid;
	}

	public void setEid(Integer eid) {
		this.eid = eid;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	@Override
	public String toString() {
		return "Emp [eid=" + eid + ", ename=" + ename + ", age=" + age
				+ ", sex=" + sex + "]";
	}
	
	
}

//测试类 测试使用增删改查操作
public class TestJdbcTemplate {

	ApplicationContext ac = new ClassPathXmlApplicationContext("jdbc.xml");
	JdbcTemplate jdbcTemplate = ac.getBean("jdbcTemplate",JdbcTemplate.class);
	
	//单行操作
	@Test
	public void testUpdate() {
//		jdbcTemplate.update("insert into emp values(null,'张三',23,'男')");
		/*String sql = "insert into emp values(null,?,?,?)";
		jdbcTemplate.update(sql, "李四",23,"女");//单个增删改  "" 字符串的标识 */
		
		//String eids = "1,2,3";//不能使用通配符的方式赋值 prepareStatement不支持批量处理 只能去除第一个
		//String sql = "delete from emp where eid in (?)";
		
		//使用单行的sql进行批量查询 
		String eids = "3,4,5";
		String sql = "delete from emp where eid in ("+ eids +")";
		jdbcTemplate.update(sql);
		
		//模糊查询 分配符
		String mohu = "a";
		//String sqls = "select * from emp where ename like '%?%'";//preparestatement默认会为字符串添加单引号
		String sqls = "select * from emp where ename like concat('%',?,'%')";
	}
	
	//批量操作
	@Test
	public void testBatchUpdate(){
		String sql = "insert into emp values(null,?,?,?)";
		List<Object[]> list = new ArrayList<>();
		list.add(new Object[]{"a1",1,"男"});
		list.add(new Object[]{"a2",2,"男"});
		list.add(new Object[]{"a3",3,"男"});
		jdbcTemplate.batchUpdate(sql, list);//批量增删改
	}
	
	//查询单个数值或单条数据
	@Test
	public void testQueryForObject(){
		//jdbcTemplate.queryForObject(sql, requiredType);获取单个的值
		//jdbcTemplate.queryForObject(sql, rowMapper);获取单条数据
		/*String sql = "select eid,ename,age,sex from emp where eid = ?";
		RowMapper<Emp> rowMapper = new BeanPropertyRowMapper<>(Emp.class);//将列名(字段名或字段名的别名)与属性名进行映射
		Emp emp = jdbcTemplate.queryForObject(sql, new Object[]{1}, rowMapper);
		System.out.println(emp);*/
		
		String sql = "select count(*) from emp";
		Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
		System.out.println(count);
	}
	
	//查询所有数据
	@Test
	public void testQuery(){
		String sql = "select eid,ename,age,sex from emp";
		RowMapper<Emp> rowMapper = new BeanPropertyRowMapper<>(Emp.class);
		List<Emp> list = jdbcTemplate.query(sql, rowMapper);
		for (Emp emp : list) {
			System.out.println(emp);
		}
	}

}

注意sql的注入问题 PrepareStatements的通配符使用问题!

spring中使用jdbc完成买书案例

CREATE TABLE book(
bid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
bname VARCHAR(10),
price INT
)AUTO_INCREMENT=1;

SELECT * FROM book

CREATE TABLE stock(
sid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
st INT
)AUTO_INCREMENT=1;

SELECT * FROM stock

CREATE TABLE money(
uid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
balance INT
)AUTO_INCREMENT=1;

SELECT * FROM money

book.xml

<context:component-scan base-package="com.atguigu.book"></context:component-scan>	
	
	<!-- 引入属性文件 -->
	<!-- <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="location" value="db.properties"></property>
	</bean> -->

	<!-- 引入属性文件 -->
	<context:property-placeholder location="db.properties"/>

	<!-- 创建数据源 -->
	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
		<property name="driverClassName" value="${jdbc.driver}"></property>
		<property name="url" value="${jdbc.url}"></property>
		<property name="username" value="${jdbc.username}"></property>
		<property name="password" value="${jdbc.password}"></property>
	</bean>

	<!-- 通过数据源配置JdbcTemplate -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>

db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm
jdbc.username=root
jdbc.password=123456

public interface BookDao {

	Integer selectPrice(String bid);
	
	void updateSt(String bid);
	
	void updateBalance(String uid,Integer price);
}


@Repository
public class BookDaoImpl implements BookDao{
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public Integer selectPrice(String bid) {
		String sql = "select price from book where bid = ?";
		Integer price = jdbcTemplate.queryForObject(sql, new Object[]{bid},Integer.class);
		return price;
	}

	@Override
	public void updateSt(String bid) {
		//获取该书籍的库存
		String sql = "select st from stock where sid = ?";
		Integer st = jdbcTemplate.queryForObject(sql,new Object[]{bid} ,Integer.class);
		if(st <= 0){
			throw new RuntimeException();
		}else{
			jdbcTemplate.update("update stock set st = st - 1 where sid = ?", bid);
		}
	}

	@Override
	public void updateBalance(String uid,Integer price) {
		Integer balance = jdbcTemplate.queryForObject("select balance from money where uid = ?", new Object[]{uid},Integer.class);
		if(balance < price){
			throw new RuntimeException();
		}else{
			jdbcTemplate.update("update money set balance = balance - ? where uid = ?",price,uid);
		}
	}
	
}

 

public interface BookService {

	void buyBook(String bid,String uid);
	
}


@Service
public class BookServiceImpl implements BookService{
	
	@Autowired
	private BookDao dao;

	@Override
	public void buyBook(String bid, String uid) {
		Integer price = dao.selectPrice(bid);
		dao.updateSt(bid);
		dao.updateBalance(uid, price);
	}
}

@Controller
public class BookController {

	@Autowired
	public BookService service;
	
	public void buyBook(){
		service.buyBook("1", "1001");
	}
	
}

public class Test {
	
	public static void main(String[] args) {
		
		ApplicationContext ac = new ClassPathXmlApplicationContext("book.xml");
		BookController controller = ac.getBean("bookController", BookController.class);
		controller.buyBook();
	}
	
}

表明没有加入自动装配的jar包 spring-aop.jar!