在上一节的基础上,本节我们来添加 关联真实数据库。
一、添加相关依赖
group 'org.happy'
version '1.0'
apply plugin: 'java'
apply plugin: 'war'
apply plugin: 'jetty'
sourceCompatibility = 1.5
repositories {
//mavenCentral()
maven { url 'http://maven.oschina.net/content/groups/public/' }
}
dependencies {
providedCompile 'javax.servlet:javax.servlet-api:3.1.0'
providedCompile 'javax.servlet.jsp:jsp-api:2.2'
runtime 'javax.servlet:jstl:1.2'
compile 'org.slf4j:slf4j-log4j12:1.7.7'
runtime 'mysql:mysql-connector-java:5.1.33'
testCompile group: 'junit', name: 'junit', version: '4.11'
}
二、创建 user 表
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`passwd` varchar(50) DEFAULT NULL,
`dept` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
三、改写 UserService
package org.happy.webdemo.service;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.DriverManager;
import org.happy.webdemo.model.User;
public class UserService {
private static final Logger LOGGER = LoggerFactory.getLogger(UserService.class);
private static final String DRV = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/jeedemo";
private static final String USR = "root";
private static final String PWD = "f3r3o2m527";
static {
try {
Class.forName(DRV);
} catch (ClassNotFoundException e) {
LOGGER.error("can not load jdbc driver", e);
}
}
public List<User> getUserList() {
Connection conn = null;
List<User> userList = new ArrayList<User>();
try {
String sql = "SELECT * from user";
conn = DriverManager.getConnection(URL, USR, PWD);
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
User user = new User();
user.setId(rs.getLong("id"));
user.setName(rs.getString("name"));
user.setPasswd(rs.getString("passwd"));
user.setDept(rs.getString("dept"));
userList.add(user);
}
} catch (SQLException e) {
LOGGER.error("execute sql failed", e);
} finally {
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
LOGGER.error("close connection failed", e);
}
}
}
return userList;
}
/*
public List<User> getUserList() {
String sql = "SELECT * FROM user";
List<User> ul = new ArrayList<User>();
User user = new User();
user.setDept("Sales");
user.setName("David");
user.setPasswd("8888");
user.setId((long) 1);
ul.add(user);
User user2 = new User();
user2.setDept("Research");
user2.setName("Tom");
user2.setPasswd("7777");
user2.setId((long) 2);
ul.add(user2);
User user3 = new User();
user3.setDept("Fin");
user3.setName("Kitty");
user3.setPasswd("6666");
user3.setId((long) 3);
ul.add(user3);
return ul;
}
*/
}
四、测试
使用MYSQL 客户端往表格中写入一些数据,然后运行该程序 (http://localhost:8080/webdemo-1.0/users)。它应该能取到你刚刚在数据库中添加的用户信息了。