Java Dev Notes - 9 - Java链接MySQL

如何配置java连接MySQL

下载：

1. MySQL 

2. mysql-connector

设置环境变量：

将msysql-connetor的zip包解压，将其路径加入环境变量classpath中

F:\Program Files\MySQL\Conector J 5.1.18\mysql-connector-java-5.1.18-bin.jar

如果环境变量设置错误就会出现改错误：

Load Driver Failed!
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

如果添加环境变量以后，还是没有修正该错误，可以在Eclipse中尝试如下方法：

右键工程名->Build Path->Config Build Path->Library->Add External Jars， 将connector的路径加入进去。

p.s. 偶碰到过这种问题，rp不好啊。

---

public class LocationDB {

	private final static String driver = "com.mysql.jdbc.Driver";
	private final static String host = "jdbc:mysql://127.0.0.1:3306/location";
	private final static String usr = "root";
	private final static String pwd = "root";
	private boolean isConnected = false;
	private Connection conn = null;

	public static void main(String[] args) {
		LocationDB test = new LocationDB();
		try {
			test.connect();
		} catch (Exception e) {
			e.printStackTrace();
		}

		try {
			String querySQL = "SELECT * FROM location.test";
			String insertSQL = "INSERT INTO location.test (value) VALUES (?)";

			PreparedStatement ps = test.getPreparedStatement(querySQL);
			ResultSet rs = test.query(ps);

			while (rs.next()) {
				int id = rs.getInt("idtest");
				String value = rs.getString("value");
				System.out.println(id + "," + value);

			}

			ps = test.getPreparedStatement(insertSQL);
			ps.setInt(1, 5);
			int ret = test.insert(ps);

			if (ret > 0) {
				ps = test.getPreparedStatement(querySQL);
				rs = test.query(ps);				
				while (rs.next()) {
					int id = rs.getInt("idtest");
					String value = rs.getString("value");
					System.out.println(id + "," + value);

				}

				while (rs.next()) {
					int id = rs.getInt("idtest");
					String value = rs.getString("value");
					System.out.println(id + "," + value);

				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		try {
			test.disConnect();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public void connect() throws SQLException {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			System.err.println("Load Driver Failed!");
			e.printStackTrace();
			return;
		}

		conn = DriverManager.getConnection(host, usr, pwd);
		if (!conn.isClosed()) {
			System.out.println("DB connected");
			isConnected = true;
		}

	}

	public boolean isConnected() {
		return isConnected;
	}

	public void disConnect() throws SQLException {
		if (isConnected == true) {
			System.out.println("DB disconnected");
			conn.close();
		}
	}

	public PreparedStatement getPreparedStatement(String sql)
			throws SQLException {
		return conn.prepareStatement(sql);
	}

	public ResultSet query(PreparedStatement ps) throws SQLException {
		ResultSet rs = ps.executeQuery();
		return rs;
	}

	public int insert(PreparedStatement ps) throws SQLException {
		return ps.executeUpdate();
	}
}

安装完MySQL后需要下载驱动, 驱动地址如下

http://dev.mysql.com/downloads/connector/j/5.0.html

链接数据库的代码

之后我做了一点小小的封装

public void connectDB() {
		String driver = "com.mysql.jdbc.Driver";
		String host = "jdbc:mysql://127.0.0.1:3306/test";
		String usr = "root";
		String pwd = "root";
		String sql = "SELECT * FROM test.test";

		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		try {
			Connection conn = DriverManager.getConnection(host, usr, pwd);
			if (!conn.isClosed())
				System.out.println("DB Connected");
			
			
			String insertSQL = "INSERT INTO test.test (value) VALUES (8)";
			PreparedStatement ps=conn.prepareStatement(insertSQL);			
			int result=ps.executeUpdate();
			if(result >0) System.out.println("insert OK");
			
			insertSQL = "INSERT INTO test.test (value) VALUES (?)";
			ps=conn.prepareStatement(insertSQL);	
			ps.setInt(1, 5);
			result=ps.executeUpdate();
			if(result >0) System.out.println("insert OK");
			
			
			Statement statment = (Statement) conn.createStatement();
			ResultSet rs = statment.executeQuery(sql);
			while (rs.next()) {
				int id = rs.getInt("idtest");
				String value = rs.getString("value");
				System.out.println(id + "," + value);
			}

			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}