学习 周 总结（2013、12、29）一

-、mySQL数据库的连接，1.先写个DBUtil连接数据库工具类，注意这里没有关闭连接con的方法，效率会更高些；2.然后是DAO的抽象类，编写所需的方法；3.DaoImpl实现类，编写具体的方法；下面贴代码：

工具类DBUtil2：

import java.sql.Connection;
import java.sql.DriverManager;

/**
 * MySql数据库工具包
 */
public class DBUtil2 {
	private static Connection con;
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager
					.getConnection(
							"jdbc:mysql://localhost:3306/SFTPNET?useUnicode=true&characterEncoding=utf-8",
							"root", "admin");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static Connection getConnection() {
		return con;
	}
}

抽象类CategoryDao：

import java.util.HashMap;
import java.util.List;

import cn.fund123.sftpnet.admin.category.entity.Category;

public interface CategoryDao {
	/**
	 * 
	 * 查找所有节点的信息
	 * */
	public abstract List<HashMap<String,Object>> findAllCategory();

	/**
	 * 
	 * 查找所有以此id为parent-id的节点
	 * */
	public abstract List<Category> findAllCategory(String string);

	/**
	 * 
	 * 根据id查找节点name
	 * */
	public abstract String findIdByName(String name);

	/**
	 * 
	 * 
	 * 删除一条category记录
	 * */
	public abstract String deleteOneCategory(String id);

	/**
	 * 
	 * 插入一条category数据
	 * */
	public abstract String insertOneCategory(Category c);

	/**
	 * 
	 * 更新一条category数据
	 * */
	public abstract String updateOneCategory(Category c);

	/**
	 * 
	 * 根据id查找节点一个category节点信息
	 * */
	public abstract Category findCategoryById(String id);
}

实现类CategoryDaoImpl：（包括数据库的增速改查等操作~~）

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

import cn.fund123.sftpnet.admin.category.dao.CategoryDao;
import cn.fund123.sftpnet.admin.category.entity.Category;
import cn.fund123.sftpnet.core.dbutils.DBUtil2;

public class CategoryDaoImpl implements CategoryDao {

	/**
	 * 
	 * 查找所有节点的信息
	 * */
	public List<HashMap<String, Object>> findAllCategory() {
		List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();

		Connection conn;
		try {
			conn = DBUtil2.getConnection();

			PreparedStatement prep = conn
					.prepareStatement("select * from category");

			// 1代表第一个问号,这里将id值设为
			// prep.setString(1, id);
			ResultSet rs = prep.executeQuery();

			while (rs.next()) {
				HashMap<String, Object> hm = new LinkedHashMap<String, Object>();
				hm.put("id", rs.getString("id"));
				hm.put("pId", rs.getString("parent_id"));
				hm.put("name", rs.getString("name"));
				hm.put("open", "true");
				list.add(hm);
			}
			prep.close();
			// DBUtil2.close(conn);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;

	}

	/**
	 * 
	 * 查找所有 以此id 为parent-id的节点
	 * */
	@Override
	public List<Category> findAllCategory(String id) {
		List<Category> list = new ArrayList<Category>();

		Connection conn;
		try {
			conn = DBUtil2.getConnection();

			PreparedStatement prep = conn
					.prepareStatement("select * from category where parent_id=?");

			// 1代表第一个问号,这里将id值设为
			prep.setString(1, id);
			ResultSet rs = prep.executeQuery();

			while (rs.next()) {
				Category cg = new Category();
				cg.setId(rs.getString("id"));
				cg.setName(rs.getString("name"));
				cg.setParent_id(rs.getString("parent_id"));
				list.add(cg);
			}
			prep.close();
			// DBUtil2.close(conn);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 
	 * 根据id查找节点name
	 * */
	@Override
	public String findIdByName(String name) {
		String id = null;
		Connection conn;
		try {
			conn = DBUtil2.getConnection();

			PreparedStatement prep = conn
					.prepareStatement("select id from category where name=?");

			// 1代表第一个问号,这里将id值设为
			prep.setString(1, name);
			ResultSet rs = prep.executeQuery();

			while (rs.next()) {
				id = rs.getString("id");
			}
			prep.close();
			// DBUtil2.close(conn);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return id;

	}

	/**
	 * 
	 * 根据id查找节点一个category节点信息
	 * */
	@Override
	public Category findCategoryById(String id) {
		Category c = new Category();
		Connection conn;
		try {
			conn = DBUtil2.getConnection();

			PreparedStatement prep = conn
					.prepareStatement("select * from category_list where id=?");

			// 1代表第一个问号,这里将id值设为
			prep.setString(1, id);
			ResultSet rs = prep.executeQuery();

			while (rs.next()) {
				c.setId(rs.getString("id"));
				c.setName(rs.getString("name"));
				c.setParent_id(rs.getString("parent_id"));
			}
			prep.close();
			// DBUtil2.close(conn);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return c;

	}

	/**
	 * 
	 * 删除一条category记录
	 * */
	@Override
	public String deleteOneCategory(String id) {
		String flag = null;
		Connection conn;
		try {
			conn = DBUtil2.getConnection();

			PreparedStatement prep = conn
					.prepareStatement("delete  from category where id=?");

			// 1代表第一个问号,这里将id值设为
			prep.setString(1, id);
			String rs = Integer.toString(prep.executeUpdate());
			if (rs != null) {
				flag = "DeletedSuccessfully";
			} else {
				flag = "DeleteFailed";
			}
			prep.close();
			// DBUtil2.close(conn);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 
	 * 插入一条category数据
	 * */
	@Override
	public String insertOneCategory(Category c) {
		Connection conn;
		// 用于表示是否插入成功
		String flag = null;
		try {
			conn = DBUtil2.getConnection();
			PreparedStatement prep = conn
					.prepareStatement("insert into category(name,parent_id,create_time,modified_time) values(?,?,?,?)");

			// 1代表第一个问号,这里将id值设为
			prep.setString(1, c.getName());
			prep.setString(2, c.getParent_id());
			prep.setTimestamp(3, new Timestamp(c.getModified_time().getTime()));
			prep.setTimestamp(4, new Timestamp(c.getModified_time().getTime()));
			String rs = Integer.toString(prep.executeUpdate());
			// 返回标志flag，（"InsertedSuccessfully"或者"InsertFailed"）,判断是否删除成功
			if (rs != null) {
				flag = "InsertedSuccessfully";
			} else {
				flag = "InsertFailed";
			}
			prep.close();
			// DBUtil2.close(conn);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;

	}

	/**
	 * 
	 * 更新一条category数据
	 * */
	@Override
	public String updateOneCategory(Category c) {
		Connection conn;
		// 用于表示是否插入成功
		String flag = null;
		String id = c.getId();
		String name = c.getName();
		String parent_id = c.getParent_id();

		try {
			conn = DBUtil2.getConnection();
			PreparedStatement prep = conn
					.prepareStatement("UPDATE category SET name=?,parent_id=?, modified_time=? where id=?");

			// 1代表第一个问号,这里将id值设为
			prep.setString(1, name);
			prep.setString(2, parent_id);
			prep.setTimestamp(3, new Timestamp(new Date().getTime()));
			prep.setString(4, id);
			String rs = Integer.toString(prep.executeUpdate());
			// 返回标志flag，（"UpdatedSuccessfully"或者"UpdateFailed"）,判断是否删除成功
			if (rs != null) {
				flag = "UpdatedSuccessfully";
			} else {
				flag = "UpdateFailed";
			}
			prep.close();
			// DBUtil2.close(conn);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;

	}

}