JFinal-连接Mysql-简单的增删改查(五)

接上篇，当我们了解完大致怎么传参，页面模板怎么使用之后，接下来就是连接到数据库了，这样，才是一个完整的流程。本实例代码地址：jfinal-demo-05-mysql-druid

数据库准备

新建数据库jfinal_demo，导入以下sql语句

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '张三', '25');
INSERT INTO `user` VALUES ('2', '李四', '22');
INSERT INTO `user` VALUES ('3', '王二', '25');
INSERT INTO `user` VALUES ('4', '铁蛋', '23');
INSERT INTO `user` VALUES ('5', '山炮', '28');

pom依赖

关键依赖：

<dependency>
    	<groupId>com.jfinal</groupId>
    	<artifactId>jetty-server</artifactId>
    	<version>2018.11</version>
    	<!--
			此处的 scope 值为 compile 仅为支持 IDEA 下启动项目
			打 war 包时需要改成 provided，以免将一些无用的 jar 打进去
		-->
    </dependency>
    
    <dependency>
    	<groupId>com.jfinal</groupId>
    	<artifactId>jfinal</artifactId>
    	<version>3.5</version>
    </dependency>
    <!-- mysql驱动 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.37</version>
    </dependency>
	<!-- druid连接池 -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.0.29</version>
    </dependency>

完整pom文件：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.perfree</groupId>
  <artifactId>jfinal-demo-05-mysql-druid</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>
  
  <dependencies>
  	<dependency>
    	<groupId>com.jfinal</groupId>
    	<artifactId>jetty-server</artifactId>
    	<version>2018.11</version>
    	<!--
			此处的 scope 值为 compile 仅为支持 IDEA 下启动项目
			打 war 包时需要改成 provided，以免将一些无用的 jar 打进去
		-->
    </dependency>
    
    <dependency>
    	<groupId>com.jfinal</groupId>
    	<artifactId>jfinal</artifactId>
    	<version>3.5</version>
    </dependency>
    <!-- mysql驱动 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.37</version>
    </dependency>
	<!-- druid连接池 -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.0.29</version>
    </dependency>
  </dependencies>
  
  <build>
    <finalName>jfinal-demo-03-controller</finalName>
	    <plugins>
	    	<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.6.1</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
					<encoding>UTF-8</encoding>
					<!-- java8 保留参数名编译参数，支持 action 带参 -->
					<compilerArgument>-parameters</compilerArgument>
					<compilerArguments>
						<verbose />
					</compilerArguments>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" version="2.5">
  <display-name>jfinal-demo-05-mysql-druid</display-name>
  <filter>
  	<filter-name>jfinal</filter-name>
  	<filter-class>com.jfinal.core.JFinalFilter</filter-class>
  	<init-param>
		<param-name>configClass</param-name>
		<param-value>com.perfree.common.DemoConfig</param-value>
	</init-param>
  </filter>
  <filter-mapping>
  	<filter-name>jfinal</filter-name>
  	<url-pattern>/*</url-pattern>
  </filter-mapping>
</web-app>

db.propertis配置文件

jdbcUrl = jdbc:mysql://localhost/jfinal_demo?characterEncoding=utf8
user = root
password = 215521
devMode = true
showSql = true

DemoConfig

com.perfree.common包下DemoConfig类：

package com.perfree.common;

import com.jfinal.config.Constants;
import com.jfinal.config.Handlers;
import com.jfinal.config.Interceptors;
import com.jfinal.config.JFinalConfig;
import com.jfinal.config.Plugins;
import com.jfinal.config.Routes;
import com.jfinal.core.JFinal;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.druid.DruidPlugin;
import com.jfinal.template.Engine;
import com.perfree.controller.UserController;
import com.perfree.model.User;

public class DemoConfig extends JFinalConfig {
	/**
	 * 启动方法
	 * @param args
	 */
	public static void main(String[] args) {
		JFinal.start("src/main/webapp", 80, "/",5);
	}
	
	/**
	 * 配置常量
	 */
	@Override
	public void configConstant(Constants me) {
		//加载外部配置文件
		loadPropertyFile("db.properties");
		me.setDevMode(true);
		//开启支持注解，支持 Controller、Interceptor 之中使用 @Inject 注入业务层，并且自动实现 AOP
		me.setInjectDependency(true);
	}

	/**
	 * 配置模板
	 */
	@Override
	public void configEngine(Engine me) {

	}

	/**
	 * 配置处理器
	 */
	@Override
	public void configHandler(Handlers me) {

	}
	
	/**
	 * 配置全局拦截器
	 */
	@Override
	public void configInterceptor(Interceptors me) {

	}

	/**
	 * 配置插件
	 */
	@Override
	public void configPlugin(Plugins me) {
		//配置druid连接池
		DruidPlugin db = new DruidPlugin(getProperty("jdbcUrl"),getProperty("user"),getProperty("password"));
		me.add(db);
		//ActiveRecord是作为JFinal的Plugin而存在的，所以使用时需要在JFinalConfig中配置ActiveRecordPlugin
		ActiveRecordPlugin activeRecordPlugin = new ActiveRecordPlugin(db);
		activeRecordPlugin.addMapping("user", User.class);
		//展示sql语句
		activeRecordPlugin.setShowSql(true);
		me.add(activeRecordPlugin);
	}
	
	/**
	 * 配置路由
	 */
	@Override
	public void configRoute(Routes me) {
		me.add("/", UserController.class,"/");
	}

}

user.html

webapp下user.html用来展示用户信息

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>mysql</title>
</head>
<style>
    .userTable {  min-height: 25px; line-height: 40px; text-align: center; border-color:#b6ff00; border-collapse: collapse;margin-top: 10px;}   
    .useradd {margin-top: 20px;text-decoration: none;}
</style>
<body>
	<a href="toAdd" class="useradd">添加用户</a>
	<table border="1" class="userTable">
		<tr>
			<th width="100px">id</th>
			<th width="150px">姓名</th>
			<th width="150px">年龄</th>
			<th width="150px">操作</th>
		</tr>
		#for(user:users)
		<tr>
			<td>#(user.id)</td>
			<td>#(user.name)</td>
			<td>#(user.age)</td>
			<td>
				<a href="/toEdit?id=#(user.id)">编辑</a>  
				<a href="/del?id=#(user.id)">删除</a>
			</td>
		</tr>
		#end
	</table>
</body>
</html>

edit.html

编辑用户界面

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>编辑用户</title>
</head>
<style>
	input{height:25px;margin-top: 10px;}
	input[type='submit'] {width: 100px;height: 30px;margin-left: 50px;}
</style>
<body>
	<form action="updateUser" method="post">
		<input type="hidden" name="user.id" value="#(user.id)" />
		姓名：<input type="text" name="user.name" autofocus="autofocus" value="#(user.name)" /><br>
		年龄：<input type="text" name="user.age" value="#(user.age)" /><br>
		<input type="submit" value="提交"/>
	</form>
</body>
</html>

add.html

增加用户界面

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加用户</title>
</head>
<style>
	input{height:25px;margin-top: 10px;}
	input[type='submit'] {width: 100px;height: 30px;margin-left: 50px;}
</style>
<body>
	<form action="addUser" method="post">
		姓名：<input type="text" name="user.name" autofocus="autofocus" placeholder="请输入姓名" /><br>
		年龄：<input type="text" name="user.age" placeholder="请输入年龄" /><br>
		<input type="submit" value="提交"/>
	</form>
</body>
</html>

User

com.perfree.model包下User类继承Model：

package com.perfree.model;

import com.jfinal.plugin.activerecord.Model;

public class User extends Model<User>{

}

UserController

com.perfree.controller包下UserController继承Controller

package com.perfree.controller;

import java.util.List;

import com.jfinal.aop.Inject;
import com.jfinal.core.Controller;
import com.perfree.model.User;
import com.perfree.service.UserService;

public class UserController extends Controller {
	
	//注入UserService
	@Inject
	UserService userService;
	
	//默认路径，查询所有用户信息并返回值user.html
	public void index() {
		List<User> users = userService.getAllUser();
		setAttr("users", users);
		render("user.html");
	}
	
	//删除用户
	public void del() {
		Integer id = getParaToInt("id");
		userService.delUserById(id);
		redirect301("/");
	}
	
	//前往添加用户页面
	public void toAdd() {
		render("add.html");
	}
	
	//添加用户
	public void addUser() {
		User user = getModel(User.class);
		userService.addUser(user);
		redirect301("/");
	}
	
	//根据id查询用户信息并前往至编辑页面
	public void toEdit() {
		Integer id = getParaToInt("id");
		User user = userService.findUserById(id);
		setAttr("user", user);
		render("edit.html");
	}
	
	//更新用户信息
	public void updateUser() {
		User user = getModel(User.class);
		userService.updateUser(user);
		redirect301("/");
	}
}

UserService

com.perfree.service包下UserService类

package com.perfree.service;

import java.util.List;

import com.perfree.model.User;

public class UserService {
	
	//声明dao
	private User userDao = new User().dao(); 

	/**
	 * 获取所有用户信息
	 * @return List<User>
	 */
	public List<User> getAllUser() {
		List<User> users = userDao.find("select *from user");
		return users;
	}
	
	/**
	 * 删除用户
	 */
	public void delUserById(Integer id) {
		userDao.deleteById(id);
	}
	
	/**
	 * 添加用户
	 * @param user
	 */
	public void addUser(User user) {
		user.save();
	}
	
	/**
	 * 根据id查询用户信息
	 * @param id
	 * @return User
	 */
	public User findUserById(Integer id) {
		User user = userDao.findById(id);
		return user;
	}
	
	/**
	 * 更新用户信息
	 * @param user
	 */
	public void updateUser(User user) {
		user.update();
		
	}

}

当我们的User类通过继承Model类后，便立即拥有的众多方便的操作数据库的方法。基于ActiveRecord的Model无需定义属性，无需定义getter、setter方法，无需XML配置，无需Annotation配置，极大降低了代码量。

运行项目

访问http://127.0.0.1/ 展示用户信息列表

点击添加用户跳转至添加用户页：

添加一个新用户小飞，年龄21，并提交： 没问题，接着删除小飞这个用户，点击删除： 删除成功，接着编辑山炮用户，点击编辑将前往编辑页： 接着修改它的年龄为28，并提交： 至此，简单的增删改查完毕。