数据库操作(存着用来复制省的每次写)

转载 于 2014-07-22 22:49:00 发布 · 68 阅读 · 0 ·
CC 4.0 BY-SA版权
原文链接：http://www.cnblogs.com/france/p/4808730.html
文章标签：

#数据库 #java #测试

本文介绍了一个基于Java的投票系统的数据库操作实现，包括配置文件解析、数据库连接建立、增删改查等基本功能，并提供了具体的代码示例。
数据库操作(存着用来复制省的每次写)

首先是：

/Vote/src/db.properties 配置文件信息(key-value形式)

driver=com.mysql.jdbc.Driver
dburl=jdbc:mysql://localhost:3306/vote?useUnicode=true&characterEncoding=utf8
dbuser=root
password=root

接着是工厂类：

/Vote/src/vote/db/ConnectionFactory.java

作用：得到配置文件的信息进行数据库的连接

package vote.db;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;



public class ConnectionFactory {
	public static String driver;
	public static  String dburl;
	public static  String dbuser;
	public static  String password;
	public static  ConnectionFactory factory = null;
	public ConnectionFactory(){
		this.getproperties();
	}
	public  void getproperties() {
		// TODO 自动生成的方法存根
		Properties prop = new Properties();//通过类方法得到Properties配置文件信息
		InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
		try {
			prop.load(inputStream);
			this.driver = prop.getProperty("driver");
			this.dburl = prop.getProperty("dburl");
			this.dbuser = prop.getProperty("dbuser");
			this.password = prop.getProperty("password");
//			this.password = "";
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection(){
		Connection conn = null;
		new ConnectionFactory();
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			conn = DriverManager.getConnection(dburl,dbuser,password);
			System.out.println("进去得到数据库");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		return conn;
	}
}

接下来是curl(增删改查) 

/Vote/src/vote/db/ControlDB.java

功能：通过自己的需求实现相应的方法，这里给出之前投票系统的一些需求方法

package vote.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;



import values.ApplyValue;
import values.VoteInfoValue;



public class ControlDB {
//	public List executeQueryRole(String sql) throws Exception {
//		ResultSet rs = null;
//		List list = new ArrayList();
//		Connection con = null;
//		Statement stmt = null;
//		try {
//			con = ConnectionFactory.getConnection();
//			stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
//					ResultSet.CONCUR_UPDATABLE);
//			rs = stmt.executeQuery(sql);
//			while (rs.next()) {
//				Role role = new Role();
//				int i = 1;
//				role.setId(rs.getInt(i++));
//				role.setAdmins(rs.getString(i++));
//				role.setPass(rs.getString(i++));
//				role.setSitename(rs.getString(i++));
//				role.setCheck(rs.getInt(i++));
//				role.setVotename(rs.getString(i++));
//				list.add(role);
//			}
//		} catch (Exception e) {
//			throw e;
//		} finally {
//			DatabaseUtils.closeObject(rs, stmt, con);
//		}
//		return list;
//	}
	public boolean executeQueryToVote(String sql) throws Exception {
		boolean temp=false;
		ResultSet rs = null;
		Connection con = null;
		PreparedStatement ps=null;
		try {
		con = ConnectionFactory.getConnection();
		ps=con.prepareStatement(sql);
		
		rs = ps.executeQuery(sql);
		System.out.println("在executeQueryToVote里面");
		while (rs.next()) {
			temp=true;
		}
		} catch (Exception e) {
			throw e;
		} finally {
			CloseDateBase.closeObject(rs, ps, con);
		}
		System.out.println(temp);
		return temp;
	}
	public List<ApplyValue> executeQueryVoteIfoVal(String sql) throws Exception {
		ResultSet rs = null;
		List list = new ArrayList();
		Connection con = null;
		PreparedStatement ps=null;
		//Statement stmt = null;
		try {
//			Class.forName(driverName);
//			//2.得到连接
//			cn=DriverManager.getConnection(url,"root","root");
//			System.out.println("111");
//			PreparedStatement ps=cn.prepareStatement(sql);
//			
//			
//			ps.setObject(1, userid);
//			ps.setObject(2, password);
//			ResultSet rs=null;
//			rs=ps.executeQuery();
//			
//			if(rs.next()){
			con = ConnectionFactory.getConnection();
			ps=con.prepareStatement(sql);
			
			rs = ps.executeQuery(sql);
			System.out.println("在得链表的db里面");
//			while(rs.next()){
//				FdyValue fv=new FdyValue();
//				fv.setT_name(rs.getString("t_name"));
//				fv.setT_password(rs.getString("t_password"));
//				fv.setT_user(rs.getString("t_user"));
//				fv.setT_grade(rs.getString("t_grade"));
//				System.out.println("执行赋值操作");
//				fdyList.add(fv);
//				
			while (rs.next()) {
				System.out.println("准备拿到");
				VoteInfoValue vote = new VoteInfoValue();
				
				vote.setvId(rs.getInt("vId"));
				vote.setvStuId(rs.getString("vStuId"));
				vote.setvApyId(rs.getString("vApyId"));
				vote.setvTime(rs.getString("vTime"));
				
				
				list.add(vote);
				System.out.println("成功拿到");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			CloseDateBase.closeObject(rs, ps, con);
		}
		return list;
	}

	public List<ApplyValue> executeQueryVote(String sql) throws Exception {
		ResultSet rs = null;
		List<ApplyValue> list = new ArrayList<ApplyValue>();
		Connection con = null;
		PreparedStatement ps=null;
		//Statement stmt = null;
		try {
//			Class.forName(driverName);
//			//2.得到连接
//			cn=DriverManager.getConnection(url,"root","root");
//			System.out.println("111");
//			PreparedStatement ps=cn.prepareStatement(sql);
//			
//			
//			ps.setObject(1, userid);
//			ps.setObject(2, password);
//			ResultSet rs=null;
//			rs=ps.executeQuery();
//			
//			if(rs.next()){
			con = ConnectionFactory.getConnection();
			ps=con.prepareStatement(sql);
			
			rs = ps.executeQuery(sql);
			System.out.println("在得链表的db里面");
//			while(rs.next()){
//				FdyValue fv=new FdyValue();
//				fv.setT_name(rs.getString("t_name"));
//				fv.setT_password(rs.getString("t_password"));
//				fv.setT_user(rs.getString("t_user"));
//				fv.setT_grade(rs.getString("t_grade"));
//				System.out.println("执行赋值操作");
//				fdyList.add(fv);
//				
			while (rs.next()) {
				System.out.println("准备拿到");
				ApplyValue vote = new ApplyValue();				
				vote.setA_id(rs.getInt("apId"));
				vote.setA_stuid(rs.getString("apStuId"));
				vote.setA_name(rs.getString("apname"));				
				vote.setA_isOk(rs.getInt("apisOK"));
				vote.setA_job(rs.getInt("apType"));	
				vote.setVcount(rs.getInt("apCount"));
				vote.setSex(rs.getString("sex"));
				vote.setZzmm(rs.getString("zzmm"));
				vote.setSzbm(rs.getString("szbm"));
				vote.setXrzw(rs.getString("xrzw"));
				vote.setFirstApp(rs.getString("firstApp"));
				vote.setSecondApp(rs.getString("secondApp"));
				vote.setDifferent(rs.getString("different"));
				vote.setActivities(rs.getString("activities"));
				vote.setWorkAndValue(rs.getString("workAndValue"));
				vote.setAwards(rs.getString("awards"));
				vote.setBirthday(rs.getString("birthday"));
				vote.setPhone(rs.getString("phone"));
				vote.setQq(rs.getString("qq"));
				vote.setScore(rs.getString("score"));
				vote.setRank(rs.getString("rank"));
				vote.setSums(rs.getString("sums"));
				vote.setSum(rs.getString("sum"));
				vote.setInfo(rs.getString("info"));
				vote.setObey(rs.getString("obey"));
				
				list.add(vote);
				System.out.println("成功拿到");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			CloseDateBase.closeObject(rs, ps, con);
		}
		return list;
	}

	public int executeQueryVoteCounts(String sql) throws Exception {
		ResultSet rs = null;
		//List<ApplyValue> list = new ArrayList<ApplyValue>();
		Connection con = null;
		PreparedStatement ps=null;
		int num = 0;
		try {
			con = ConnectionFactory.getConnection();
			ps=con.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			while (rs.next()) {
				num = rs.getInt(1);
			}
			System.out.println("得到sum了");
			System.out.println(num);
		} catch (Exception e) {
			throw e;
		} finally {
			CloseDateBase.closeObject(rs, ps, con);
		}
		return num;
	}
	public String GetexecuteQueryShuJiStudentName(String sql) throws Exception {
		ResultSet rs = null;
		//List<ApplyValue> list = new ArrayList<ApplyValue>();
		Connection con = null;
		PreparedStatement ps=null;
		String name=null;
		try {
			con = ConnectionFactory.getConnection();
			ps=con.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			while (rs.next()) {
				name = rs.getString("sname");//这边可能需要修改
			}
			System.out.println("得到sum了");
			System.out.println(name);
		} catch (Exception e) {
			throw e;
		} finally {
			CloseDateBase.closeObject(rs, ps, con);
		}
		return name;
	}

	public void executeUpdate(String sql) throws Exception {
		//ResultSet rs = null;
		Connection con = null;
		PreparedStatement ps=null;
		
		
		try {
			con = ConnectionFactory.getConnection();
			ps=con.prepareStatement(sql);
			ps.executeUpdate(sql);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			CloseDateBase.closeObject( ps, con);
		}
	}
}

最后记得数据的关闭

/Vote/src/vote/db/CloseDateBase.java

package vote.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class CloseDateBase {

	public static void closeObject(ResultSet rs, PreparedStatement ps, Connection con) {
		closeObject(rs);
		closeObject(ps, con);
	}

	public static void closeObject(PreparedStatement ps, Connection con) {
		closeObject(ps);
		closeObject(con);
	}

	public static void closeObject(Connection con) {
		try {
			if (con != null) {
				con.close();
			}
		} catch (Exception e) {
		}
	}

	public static void closeObject(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (Exception e) {
		}
	}

	public static void closeObject(PreparedStatement ps) {
		try {
			if (ps != null) {
				ps.close();
			}
		} catch (Exception e) {
		}
	}

}


到此就差不多结束了；当然为了让读者认识更深入

讲下mvc 

一般 ：

创建model

userDAO类写接口

userDAOIMPL实现接口

这些接口要的方法可以就是上面数据库增删改查的方法；

举个例子


package vote.dao;

public interface UserDao {
	public String checkRegister(String stuid);//根据学号得到用户名判断用户名是否一样
	public boolean checkRegStuid(String stuid);//判断学号是否存在
public boolean checkInfo(String stuid,String password);
public void updateLoginTime(String stuid);
public void insertNewUser(String stuid,String stuName,String password, String email);

}



package dao.impl;

import vote.dao.UserDao;
import vote.db.ControlDB;
import vote.utils.ConvertUtil;
//用户信息
public class UserDaoImpl implements UserDao {
	ControlDB controlDB = null;

	public UserDaoImpl() {
		controlDB = new ControlDB();
	}
	@Override
	public boolean checkInfo(String stuid, String password) {
		boolean temp=false;
		
		String sql = "  SELECT * FROM `UserInfo` WHERE `stuId` = "+stuid+" and `stuPass` ="+"'"+password+"'";
		System.out.println(sql);
		try {
			temp = controlDB.executeQueryToVote(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return temp;
	}


	public void updateLoginTime(String stuid) {
		// TODO 自动生成的方法存根
		ConvertUtil cu=new ConvertUtil();
		String nowtime=cu.getTime();
		String sql = " UPDATE `UserInfo` SET `lastLogin`='"+nowtime+"' WHERE `stuId` ="
				+ stuid;
		System.out.println("the update sql="+sql);
		try {
			System.out.println("执行更新语句");
			controlDB.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	@Override
	public void insertNewUser(String stuid, String stuName, String password ,String email) {
		// TODO 自动生成的方法存根
		ConvertUtil cu=new ConvertUtil();
		String nowtime=cu.getTime();
		String sql = "insert  into UserInfo values (null,'"+stuName+"','"+password+"',"+"'"+nowtime+"','"+stuid+"','"+email+"')";
		System.out.println(sql);
		try {
			System.out.println("执行增加user语句");
			controlDB.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	@Override
	public String checkRegister(String stuid) {
		String name=null;
		
		String sql = "  SELECT * FROM `student` WHERE `sno` = "+stuid;
		System.out.println(sql);
		try {
			name = controlDB.GetexecuteQueryShuJiStudentName(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return name;
	}
	@Override
	public boolean checkRegStuid(String stuid) {
		boolean temp=false;
		
		String sql = "  SELECT * FROM `userInfo` WHERE `stuId` = "+stuid;
		System.out.println(sql);
		try {
			temp = controlDB.executeQueryToVote(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return temp;
	}

}

如果还有什么疑问 欢迎拍砖^_^

版权声明：本文为博主原创文章，未经博主允许不得转载。

posted on 2014-07-22 22:49 france 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/france/p/4808730.html

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值