WEB框架研究笔记三（连接数据库）
前面的例子是EXTJS访问STRUTS的ACTION,ACTION里面的内容是写死的字符串。接下去的工作就是需要从数据库检索数据。
1.建立数据库，（采用ACCESS比较方便一点）
就建一个student的表吧简单点。
数据库放到发布目录下好了。
2.为这个表创建一个BEAN(其实是PO)
package bean;
public class Student {
private int id;
private String name;
private String descn;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDescn() {
return descn;
}
public void setDescn(String descn) {
this.descn = descn;
}
}
3.写一个DAO
package dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import bean.Student;
public class StudentDao {
public List getStudents(Connection con) throws SQLException{
List students = new ArrayList();
Statement st = con.createStatement();
String sql = "select * from students";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setDescn(rs.getString("descn"));
students.add(student);
}
return students;
}
public int getStudentNum(Connection con) throws SQLException{
Statement st = con.createStatement();
String sql = "select count(*) from students ";
ResultSet rs = st.executeQuery(sql);
int num = 0 ;
while(rs.next()){
num = rs.getInt(1);
}
return num;
}
}
4.再写一个Service
package biz;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import util.DBUtil;
import dao.StudentDao;
public class StudentService {
private StudentDao dao = new StudentDao();
public List getStudents(){
List students = null;
Connection con = null;
try {
con = DBUtil.getConnection();
students = dao.getStudents(con);
} catch (Exception e) {
students = new ArrayList();
e.printStackTrace();
} finally{
try {
if(con != null){con.close();}
} catch (SQLException e) {}
}
return students;
}
public int getStudentNum(){
int StudentNum = 0;
Connection con = null;
try{
con = DBUtil.getConnection();
StudentNum = dao.getStudentNum(con);
} catch(Exception e){
e.printStackTrace();
}finally{
try {
if(con != null){con.close();}
} catch (SQLException e) {}
}
return StudentNum;
}
}
5.倒入上次写的两个UTIL:
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
// static String jdbcURL = "jdbc:mysql://127.0.0.1:3306/book";
// static String jdbcDriver = "com.mysql.jdbc.Driver";
// static String userName = "root";
// static String password = "root";
static String jdbcURL = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};" +
"DBQ=D:/HL/NoUse/Struts/strutsProj/WebContent/EmptyDb.mdb";
static String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
static String userName = "dba";
static String password = "";
/**
* 获取数据库连接对象
* @return 数据库连接对象
* @throws ClassNotFoundException
* @throws SQLException
*/
public static Connection getConnection() throws ClassNotFoundException,
SQLException {
Class.forName(jdbcDriver);
return DriverManager.getConnection(jdbcURL, userName, password);
}
}
package util;
import java.util.ArrayList;
import java.util.List;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import com.thoughtworks.xstream.XStream;
import com.thoughtworks.xstream.io.xml.DomDriver;
/**
* Title: Ext JS 辅助类
* Description: 该类用于转换java对象为XML文件格式或JSON文件格式
* @author weijun
* @time: 2008.07.09
*/
public class ExtHelper {
/**
* 通过List生成XML数据
* @param recordTotal 记录总数，不一定与beanList中的记录数相等
* @param beanList 包含bean对象的集合
* @return 生成的XML数据
*/
public static String getXmlFromList(long recordTotal , List beanList) {
Total total = new Total();
total.setResults(recordTotal);
List results = new ArrayList();
results.add(total);
results.addAll(beanList);
XStream sm = new XStream(new DomDriver());
for (int i = 0; i < results.size(); i++) {
Class c = results.get(i).getClass();
String b = c.getName();
String[] temp = b.split("\\.");
sm.alias(temp[temp.length - 1], c);
}
String xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n" + sm.toXML(results);
return xml;
}
/**
* 通过List生成XML数据
* @param beanList 包含bean对象的集合
* @return 生成的XML数据
*/
public static String getXmlFromList(List beanList){
return getXmlFromList(beanList.size(),beanList);
}
/**
* 通过List生成JSON数据
* @param recordTotal 记录总数，不一定与beanList中的记录数相等
* @param beanList 包含bean对象的集合
* @return 生成的JSON数据
*/
public static String getJsonFromList(long recordTotal , List beanList){
TotalJson total = new TotalJson();
total.setTotalProperty(recordTotal);
total.setRoot(beanList);
JSONObject JsonObject = JSONObject.fromObject(total);
System.out.print(JsonObject.toString());
return JsonObject.toString();
}
/**
* 通过List生成JSON数据
* @param beanList 包含bean对象的集合
* @return 生成的JSON数据
*/
public static String getJsonFromList(List beanList){
return getJsonFromList(beanList.size(),beanList);
}
/**
* 通过bean生成JSON数据
* @param bean bean对象
* @return 生成的JSON数据
*/
public static String getJsonFromBean(Object bean){
JSONObject JsonObject = JSONObject.fromObject(bean);
return JsonObject.toString();
}
}
6.OK,准备工作结束，开始最后代码修改。
修改GridAction的Execute方法。
public String execute() throws Exception
{
HttpServletResponse response = ServletActionContext.getResponse();
try{
List students = service.getStudents();
String json = ExtHelper.getJsonFromList(service.getStudentNum(), students);
response.setContentType("text/html; charset=UTF-8");
System.out.print(json);
response.getWriter().write(json);
}
catch (Exception ex){
}
return null;
}
--------------------------------------------------------------
这样就实现了从数据库取数据返回前台的功能。
接下去就是要开始加入spring和hibernate
