Spring--数据库开发--总结
Spring JDBC最核心的东西就是JDBCTemplate;
JDBCTemplate最核心的就是xml文件的配置以及三个常用的方法update(),execute(),query();
#1.配置文件ApplicationContext.xml模板(还有具体代码:)
(简要总结:
1.配置数据源;
2.配置jdbcTemplate,并且关联到数据源;
3.配置注入类(就是本例中的userDaoImpl),方便进行数据操作,同事通过<bean></bean>中的<property>进行注入类与jdbcTemplate进行关联);
最后三者都关联起来(数据源、jdbcTemplate、注入类)。
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<!-- 1.配置数据源 -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<!-- 数据库驱动 -->
<property name="driverClassName"
value="com.mysql.jdbc.Driver"></property>
<!-- 连接数据库的路径URL -->
<property name="url" value="jdbc:mysql://localhost/spring"></property>
<!--连接数据库的用户名 -->
<property name="username" value="root"></property>
<!--连接数据库的密码 -->
<property name="password" value="root"></property>
</bean>
<!-- 2.配置jdbc Template -->
<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 默认必须使用数据源 -->
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 3.配置注入类 -->
<bean id="xxx" class="xxxxx">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
</beans>
#1.2 applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<!-- 1.配置数据源 -->
<bean id="databaseSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName"
value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/spring" />
<property name="username" value="root" />
<property name="password" value="root" />
</bean>
<!-- 2.进行JDBC template配置 -->
<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="databaseSource" />
</bean>
<bean id="userDaoImpl" class="cn.itcast.jdbc.UserDaoImpl">
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>
</beans>
#2.bean的使用,例如类UserDaoImpl实现UserDao
#2.1 User的代码
package cn.itcast.jdbc;
/*
* 实体类User
*/
public class User {
private int userid; //用户id
private String username; //用户名
private String password; //密码
public int getUserid() {
return userid;
}
public void setUserid(int userid) {
this.userid = userid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User [userid=" + userid + ", username=" + username + ", password=" + password + "]";
}
}
#2.2 UserDao的代码
package cn.itcast.jdbc;
import java.util.List;
import org.junit.validator.PublicClassValidator;
public interface UserDao {
//添加用户
public int addUser(User user);
//更新用户
public int updateUser(User user);
//删除用户
public int deleteUserById(int id);
//根据id查询
public User findUserById(int id);
//查询所有账户
public List<User> findAllUser();
}
#2.3UserDaoImpl的代码
(简单总结:一共三个方法:update(),execute(),query();
其中execute()主要执行SQL语句,例如创建数据表等,
update()主要执行-增加操作add,更新操作Update,删除操作delete等SQL语句,
query()主要执行SQL查询语句,查询单个findById,查询所有findAll)
package cn.itcast.jdbc;
import java.util.List;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
public class UserDaoImpl implements UserDao {
//定义JdbcTemplate属性及其getter和setter方法
private JdbcTemplate jdbcTemplate;
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
//添加用户
@Override
public int addUser(User user) {
String sql="insert into t_user(username,password)values(?,?)";
Object[] obj =new Object[] {user.getUsername(),user.getPassword()};
//返回影响的行数
int i =this.jdbcTemplate.update(sql,obj);
return i;
}
//更新用户信息
@Override
public int updateUser(User user) {
String sql = "update t_user set username=?,password=? where userid=?";
Object[] obj = new Object[] {user.getUsername(),user.getPassword(),user.getUserid()};
int i = this.jdbcTemplate.update(sql,obj);
return i;
}
//删除信息
@Override
public int deleteUserById(int id) {
String sql ="delete from t_user where userid=?";
int i=this.jdbcTemplate.update(sql, id);
return i;
}
//通过jdbcTemplate进行相关的查询ById
@Override
public User findUserById(int id) {
//定义SQL语句
String sql="select * from t_user where userid=?";
//将结果集通过Java的反射机制映射到java对象中
RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
//将ID绑定到SQL语句中,并通过RowMapper返回一个Object类型的单行记录
return this.jdbcTemplate.queryForObject(sql, rowMapper, id);
}
//进行ByAll查询
@Override
public List<User> findAllUser() {
//定义SQL语句
String sql="select * from t_user";
RowMapper<User> rowMapper=new BeanPropertyRowMapper<User>(User.class);
//query()方法执行查询,并返回一个集合
return this.jdbcTemplate.query(sql, rowMapper);
}
}
#3.测试代码
package cn.itcast.jdbc;
import java.util.List;
import javax.sound.midi.VoiceStatus;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
/*
* 使用excute()方法建表
*/
public class JdbcTemplateTest {
@Test
public void createTableTest() {
// 定义配置文件路径
String xmlPath = "cn/itcast/jdbc/ApplicationContext.xml";
// 加载配置文件
ApplicationContext applicationContext = new ClassPathXmlApplicationContext(xmlPath);
// 获取jdbcTemple实例
JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
// 使用execute方法执行SQL语句,创建数据表
jdbcTemplate.execute("create table t_user(" + "userid int primary key auto_increment," + "username varchar(50),"
+ "password varchar(32));");
}
//添加 测试
@Test
public void addUserTest() {
// 定义配置文件路径
String xmlPath = "cn/itcast/jdbc/ApplicationContext.xml";
// 创建spring容器,加载配置文件
ApplicationContext applicationContext = new ClassPathXmlApplicationContext(xmlPath);
// 获取UserDao的实例
UserDao userDao = (UserDao) applicationContext.getBean("userDaoImpl");
User user = new User();
user.setUsername("Lili");
user.setPassword("root");
// 调用addUser方法,获取返还的结果
int rows = userDao.addUser(user);
if (rows == 1) {
System.out.println("添加用户数据成功!");
} else {
System.out.println("操作失败!");
}
}
//更新 测试
@Test
public void updateUserTest() {
// 定义配置文件路径
String xmlPath = "cn/itcast/jdbc/ApplicationContext.xml";
// 创建spring容器,加载配置文件
ApplicationContext applicationContext = new ClassPathXmlApplicationContext(xmlPath);
// 获取UserDao的实例
UserDao userDao = (UserDao) applicationContext.getBean("userDaoImpl");
User user = new User();
user.setUserid(1);
user.setUsername("Toni");
user.setPassword("123");
// 调用addUser方法,获取返还的结果
int rows = userDao.updateUser(user);
if (rows == 1) {
System.out.println("更新用户数据成功!");
} else {
System.out.println("更新操作失败!");
}
}
//删除操作
@Test
public void deleteUserTest() {
// 定义配置文件路径
String xmlPath = "cn/itcast/jdbc/ApplicationContext.xml";
// 创建spring容器,加载配置文件
ApplicationContext applicationContext = new ClassPathXmlApplicationContext(xmlPath);
// 获取UserDao的实例
UserDao userDao = (UserDao) applicationContext.getBean("userDaoImpl");
User user = new User();
user.setUserid(1);
user.setUsername("Toni");
user.setPassword("123");
// 调用addUser方法,获取返还的结果
int rows = userDao.deleteUserById(4);
if (rows == 1) {
System.out.println("删除用户数据成功!");
} else {
System.out.println("删除操作失败!");
}
}
@Test
public void findUserByIdTest() {
String xmlPath ="cn/itcast/jdbc/ApplicationContext.xml";
ApplicationContext applicationContext = new ClassPathXmlApplicationContext(xmlPath);
UserDao userDao =(UserDao) applicationContext.getBean("userDaoImpl");
User user=userDao.findUserById(7);
System.out.println(user);
}
@Test
public void findAllTest() {
String xml="cn/itcast/jdbc/ApplicationContext.xml";
ApplicationContext applicationContext =new ClassPathXmlApplicationContext(xml);
UserDao userDao =(UserDao) applicationContext.getBean("userDaoImpl");
List<User> list=userDao.findAllUser();
for (User user : list) {
System.out.println(user);
}
}
}
本博文仅作为本人学习总结,难免会出错,欢迎各位吐槽!共同进步!