最近在学习spring,学习使用springMVC搭建后台应用,以下是关于在这个框架里面使用MySQL的记录,方便大家一起学习。
环境:
eclipse neon版本
Tomcat v9.0
JDK 1.8
linux Ubuntu 16.04
全部代码见github,文末有地址
搭建MySQL
Ubuntu下三条命令即可安装配置成功:
1. sudo apt-get install mysql-server
2. sudo apt-get install mysql-client
3. sudo apt-get install libmysqlclient-dev
安装,启动等操作可参考我之前的博客:http://www.cnblogs.com/linchw3/p/6195677.html
window系统下的安装配置可自己百度~~
在数据库中建库,建表
我建的数据库为springmvc,表为engine
在spring的配置文件中进行相应的配置:
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql:///springmvc?useUnicode=true&characterEncoding=utf8" />
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="EngineDao" class="cn.javass.chapter4.service.EngineDao">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
说明:EnginoDao是我的数据库处理类,在第三个bean中我为他注入了模板,然后在第二个bean中,我为模板注入了数据源,在数据源中,我交代了地址,名字密码,直接写在这里并不好,更好的办法是写在properties里面,然后在这里引入
开始创建数据库处理类
推荐先创建一个接口:
public interface EngineDaoImp {
public void insert(EngineModel model);
public void delete(String name);
public void update(EngineModel model);
public EngineModel select(String name);
public Collection<EngineModel> find();
//public EngineModel selectByName(String name,String pwd);
}
然后创建实现类,即我上面说到的EngineDao,这里我继承了JdbcDaoSupport,这是spring里面的一个类,可以很大程度方便我们的操作:
public class EngineDao extends JdbcDaoSupport implements EngineDaoImp{
@Override
public void insert(EngineModel model) {
// TODO Auto-generated method stub
String sql = "insert into engine(name, state, info) values(?,?,?)";
this.getJdbcTemplate().update(sql, model.getEnginename(), model.getEnginestate(),model.getEngineInfo());
}
@Override
public void delete(String name) {
// TODO Auto-generated method stub
String sql = "delete from engine where name=?";
this.getJdbcTemplate().update(sql, name);
}
@Override
public void update(EngineModel model) {
// TODO Auto-generated method stub
String sql = "update engine set state=?,info=? where name=?";
this.getJdbcTemplate().update(sql, model.getEnginestate(), model.getEngineInfo(),model.getEnginename());
}
@SuppressWarnings("unchecked")
@Override
public EngineModel select(String name) {
// TODO Auto-generated method stub
String sql="select * from engine where name=?";
//return this.getJdbcTemplate().queryForObject(sql, ParameterizedBeanPropertyRowMapper.newInstance(EngineModel.class),name);
return this.getJdbcTemplate().queryForObject(sql, new EngineMapper(),name);
}
@SuppressWarnings("unchecked")
@Override
public Collection<EngineModel> find() {
// TODO Auto-generated method stub
String sql="select * from engine";
return this.getJdbcTemplate().query(sql, new EngineMapper());
}
/*
*
* 因为query方法不能直接放回一个数组,所以我们只能通过rowMapper赋值;
*
* RowMapper可以将数据中的每一行封装成用户定义的类,在数据库查询中,如果返回的类型是用户自定义的类型则需要包装
*/
private static final class EngineMapper implements RowMapper{
@Override
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
// TODO Auto-generated method stub
EngineModel model = new EngineModel();
model.setEnginename(rs.getString("name"));
model.setEnginestate(rs.getString("state"));
model.setEngineInfo(rs.getString("info"));
return model;
}
}
}
这里面有关的接口的使用可以参考一下文档:
http://docs.spring.io/spring-framework/docs/2.5.x/api/org/springframework/jdbc/core/JdbcTemplate.html
上面应该注意的是创建的映射类,这可以让数据的每一行映射成我们的数据类型进行返回,方便我们的查找操作,所以在上面那些用到返回数据类型的都会用到这个类
在控制器中使用
在控制器中直接用就可以了,然后记得加上标签使之可以自动实现依赖注入
@Autowired
private EngineDao dao;
在这个例子中的控制器如下:可以实现展示和增删改查:
@Controller
public class EngineManageController {
private EngineService engineService = new EngineService();
@Autowired
private EngineDao dao;
public EngineDao getDao() {
return dao;
}
public void setDao(EngineDao dao) {
this.dao = dao;
}
@SuppressWarnings("unchecked")
@RequestMapping("/")
public String index(Model model) {
//model.addAttribute("list", Lists.newArrayList("a", "b", "c"));
//List<EngineModel> list = dao.find();
Collection<EngineModel> col = dao.find();
List<EngineModel> list = new ArrayList<EngineModel>();
EngineModel engine;
for (EngineModel temp : col) {
engine = new EngineModel();
engine.setEnginename(temp.getEnginename());
engine.setEnginestate(temp.getEnginestate());
engine.setEngineInfo(temp.getEngineInfo());
list.add(temp);
}
System.out.println(list.size());
model.addAttribute("list",list);
return "index";
}
@RequestMapping(value="/create1", method = {RequestMethod.GET})
public ModelAndView createusemysql(HttpServletRequest request){
ModelAndView modelAndView = new ModelAndView();
modelAndView.addObject("enginemodel", new EngineModel());
modelAndView.setViewName("create");
return modelAndView;
}
@RequestMapping(value="/create1", method = {RequestMethod.POST})
public String createbypostusemysql(HttpServletRequest request,Model model,EngineModel enginemodel){
dao.insert(enginemodel);
/*Collection<EngineModel> col = dao.find();
List<EngineModel> list = new ArrayList<EngineModel>();
EngineModel engine;
for (EngineModel temp : col) {
engine = new EngineModel();
engine.setEnginename(temp.getEnginename());
engine.setEnginestate(temp.getEnginestate());
engine.setEngineInfo(temp.getEngineInfo());
list.add(temp);
}
ModelAndView modelAndView = new ModelAndView();
modelAndView.addObject("engineList", list);
modelAndView.setViewName("index"); */
return "redirect:/";
}
@RequestMapping(value="/update1/{enginename}", method = {RequestMethod.GET})
public ModelAndView updatebygetusemysql(HttpServletRequest request ,@PathVariable(value="enginename") String enginename){
ModelAndView modelAndView = new ModelAndView();
EngineModel engine = dao.select(enginename);
//System.out.println(engine.getEnginename() + " " + engine.getEngineInfo());
modelAndView.addObject("enginemodel", engine);
modelAndView.setViewName("update");
return modelAndView;
}
@RequestMapping(value="/update1/{enginename}", method = {RequestMethod.POST})
public String updatebypostusemysql(HttpServletRequest request, Model model ,EngineModel enginemodel, @PathVariable(value="enginename") String enginename){
enginemodel.setEnginename(enginename);
//engineService.update(enginemodel);
dao.update(enginemodel);
/*Collection<EngineModel> col = dao.find();
List<EngineModel> list = new ArrayList<EngineModel>();
EngineModel engine;
for (EngineModel temp : col) {
engine = new EngineModel();
engine.setEnginename(temp.getEnginename());
engine.setEnginestate(temp.getEnginestate());
engine.setEngineInfo(temp.getEngineInfo());
list.add(temp);
}
model.addAttribute("engineList", list);*/
return "redirect:/";
}
@RequestMapping(value="/info1/{enginename}", method = {RequestMethod.GET})
public ModelAndView infobygetusemysql(HttpServletRequest request ,@PathVariable(value="enginename") String enginename){
ModelAndView modelAndView = new ModelAndView();
EngineModel engine = dao.select(enginename);
System.out.println(engine.getEnginename() + " " + engine.getEngineInfo());
modelAndView.addObject("enginemodel", engine);
modelAndView.addObject("thename", engine.getEnginename());
modelAndView.setViewName("info");
return modelAndView;
}
@RequestMapping(value="/delete1/{enginename}", method = {RequestMethod.GET})
public String deletebygetusemysql(HttpServletRequest request ,Model model,@PathVariable(value="enginename") String enginename){
//EngineModel engine = dao.select(enginename);
//engineService.delete(engine);
dao.delete(enginename);
//model.addAttribute("engineList", engineService.list());
return "redirect:/";
}
}
其他部分包括前端等可见我的 github。里面有整个项目的代码:https://github.com/linchw3/SpringMVC_Demo
注:我前端使用的是thymeleaf这个spring很推荐的模板语言,也可以直接用jsp的...
效果图如下:
可进行增删改查: