JDBC MySql

注意：

启动项目前要Class.forName(driverClass);

关闭掉Connection，PreparedStatement，resultset，关闭时要判断是否为空

一般都要用PreparedStatement，设置Connection的自动提交

1.静态代码块

读取配置文件中的信息：

driverClass=org.gjt.mm.mysql.Driver
user=root
password=123
url=jdbc\:mysql\://localhost\:3306/associations

package com.gt.qq.server.util;

import java.io.IOException;
import java.util.Properties;

public class PropertiesUtil {
	
	//文件名
	private static final String filename="server.properties";
	private static Properties pro;
	
	//通过静态代码块来读取配置文件
	static{
		pro=new Properties();
		try {
			pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream(filename));
		} catch (IOException e) {
			System.out.println("配置文件读取失败！！！");
			e.printStackTrace();
		}
	}
	
	/**
	 * 读取配置文件获得相关信息丰富
	 * @param key配置文件中的key
	 * @return返回结果如果有返回对应字符串，如果没有返回null
	 */
	public static String readPro(String key)
	{
		return pro.getProperty(key);
	}
}

其他读取配置文件信息的方法：

InputStream is = PropertiesUtil.class.getClassLoader().getResourceAsStream("GMServerInstance.properties");
Properties pro = new Properties();
pro.load(is);

与数据库建立连接

package com.gt.qq.server.util;

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

public class DBUtil {
	
	private static String driverClass;
	private static String user;
	private static String password;
	private static String url;
	
	static{
		driverClass=PropertiesUtil.readPro("driverClass");
		user=PropertiesUtil.readPro("user");
		password=PropertiesUtil.readPro("password");
		url=PropertiesUtil.readPro("url");
		try {
			Class.forName(driverClass);
		} catch (ClassNotFoundException e) {
			System.out.println("启动加载失败！！");
			e.printStackTrace();
		}
	}
	
	public static Connection getConn() throws SQLException
	{
		return DriverManager.getConnection(url, user, password);
	}
	
}

使用数据库连接

package com.gt.qq.server.dao.impl;

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

import com.gt.qq.server.dao.dao.UserDAO;
import com.gt.qq.server.util.DBUtil;
import com.gt.qq.vo.FindMsg;
import com.gt.qq.vo.User;

public class UserDAOImpl implements UserDAO {

	private Connection conn;
	private PreparedStatement pst;
	private ResultSet rs;
	private String sql;
	private List<User> flist;

	public User queryByAccount(String account,String password) {
		sql = "select * from user where account = ? and password = ?";
		try {
			conn = DBUtil.getConn();
			pst = conn.prepareStatement(sql);
			pst.setString(1, account);
			pst.setString(2, password);
			rs = pst.executeQuery();
			if (rs.next()) {
				User u = new User();
				u.setAccount(rs.getString("account"));
				u.setPassword(rs.getString("password"));
				u.setNickname(rs.getString("nickname"));
				u.setAge(rs.getInt("age"));
				u.setEmail(rs.getString("email"));
				u.setImg(rs.getString("img"));
				u.setFriends(this.findFriends(account));// 设置好友
				return u;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public boolean updateUser(User u) {
		return false;
	}

	public List<User> findFriends(String account) {
		flist = new ArrayList<User>();
		sql = "select * from user where account in (select fid from friends where uid = ?)";
		try {
			conn = DBUtil.getConn();
			pst = conn.prepareStatement(sql);
			pst.setString(1, account);
			rs = pst.executeQuery();
			while (rs.next()) {
				User u = new User();
				u.setAccount(rs.getString("account"));
				u.setPassword(rs.getString("password"));
				u.setNickname(rs.getString("nickname"));
				u.setAge(rs.getInt("age"));
				u.setEmail(rs.getString("email"));
				u.setImg(rs.getString("img"));
				flist.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll();
		}
		return flist;
	}

	@Override
	public boolean addUser(User u) {
		sql = "insert into user values (?,?,?,?,?,?)";
		try {
			conn = DBUtil.getConn();
			pst = conn.prepareStatement(sql);
			pst.setString(1, u.getAccount());
			pst.setString(2, u.getPassword());
			pst.setString(3, u.getNickname());
			pst.setInt(4, u.getAge());
			pst.setString(5, u.getEmail());
			pst.setString(6, u.getImg());
			pst.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll();
		}
		return false;
	}

	@Override
	public int getNextAccount() {
		int i = -1;
		sql = "select * from account";
		try {
			conn = DBUtil.getConn();
			conn.setAutoCommit(false);
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			if (rs.next()) {
				i = rs.getInt("id") + 1;
				sql = "update account set id = ?";
				pst = conn.prepareStatement(sql);
				pst.setInt(1, i);
				pst.executeUpdate();
				conn.commit();
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			closeAll();
		}
		return i;
	}

	/**
	 * 关闭所有打开资源
	 */
	private void closeAll() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (pst != null) {
				pst.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public List<User> findUsers(FindMsg fMsg) {
		flist = new ArrayList<User>();
		if (fMsg.getKey() == null) {// 查询所有用户
			return findUsers(fMsg.getU());
		} else {// 按条件查询
			sql = "select * from user where account = ?";
		}
		try {
			conn = DBUtil.getConn();
			pst = conn.prepareStatement(sql);
			pst.setString(1, fMsg.getKey());
			rs = pst.executeQuery();
			if (rs.next()) {
				User u = new User();
				u.setAccount(rs.getString("account"));
				u.setPassword(rs.getString("password"));
				u.setNickname(rs.getString("nickname"));
				u.setAge(rs.getInt("age"));
				u.setEmail(rs.getString("email"));
				u.setImg(rs.getString("img"));
				flist.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll();
		}
		return flist;
	}

	private List<User> findUsers(User user) {
		flist = new ArrayList<User>();
		sql = "select * from user where account not in ( select fid from friends where uid = ?) and account != ?";
		try {
			conn = DBUtil.getConn();
			pst = conn.prepareStatement(sql);
			pst.setString(1, user.getAccount());
			pst.setString(2, user.getAccount());
			rs = pst.executeQuery();
			while (rs.next()) {
				User u = new User();
				u.setAccount(rs.getString("account"));
				u.setPassword(rs.getString("password"));
				u.setNickname(rs.getString("nickname"));
				u.setAge(rs.getInt("age"));
				u.setEmail(rs.getString("email"));
				u.setImg(rs.getString("img"));
				flist.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeAll();
		}
		return flist;
	}

	@Override
	public void addFriend(User u, User f) {
		sql="insert into friends values (?,?)";
		try {
			conn = DBUtil.getConn();
			conn.setAutoCommit(false);
			pst = conn.prepareStatement(sql);
			pst.setString(1, u.getAccount());
			pst.setString(2, f.getAccount());
			pst.executeUpdate();
			pst.clearParameters();
			pst.setString(1,f.getAccount() );
			pst.setString(2, u.getAccount());
			pst.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			closeAll();
		}
	}
	
}