SQL: Java 连接 MySQL

1. 开发环境

- java: Eclipse

- MySQL 5.7

- MySQL Connect/J

具体的开发环境搭建就不在这里赘述。

 2.  Java连接Mysql步骤

将Eclipse 项目中加入jar (connector J): 项目属性 -> Java Biuld Path -> add external JARs....

准备MySQL, 

<span style="font-size:18px;">mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb;
Database changed
mysql> create table test_tbl
    -> (id serial,
    -> name varchar(20) not null);
Query OK, 0 rows affected (0.10 sec)

mysql> desc test_tbl;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.04 sec)

mysql></span>

Java 程序：

我建了两个类， DBHelper.java 和 Test.java

DBHelper.java 文件如下：

<span style="font-size:18px;">package SQL.Demo;
import java.sql.*;
import static java.lang.System.out;

public class DBHelper {
	public static final String driver = "com.mysql.jdbc.Driver";
	//url指向要访问的数据库
	public  Connection conn = null;
	public  PreparedStatement pstmt = null;
	public  Statement stmt = null;
	public  ResultSet res = null;
	
	public void Connect(String url, String user, String passward){
		
		try {
			// 利用forname 实现动态加载类
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, passward);
			stmt = conn.createStatement();
		} catch (ClassNotFoundException e) 
		{
			e.printStackTrace();
		} catch (SQLException e){
			e.printStackTrace();
		}

	}
	
	public void Close(){
		try {
		if (!conn.isClosed())
			conn.close();
		}
		catch (SQLException e){
			e.printStackTrace();
		}
	}
	

	public void Excute(String sql){
		try{	
			stmt.execute(sql);
		}catch (SQLException e){
			e.printStackTrace();
		}
	}
	
	public void ExcuteQuery(String sql){
		try{	
			res = stmt.executeQuery(sql);
		}catch (SQLException e){
			e.printStackTrace();
		}
	}
	
	
	public void ExcutePreparedStmt(String sql){
		try {
			pstmt = conn.prepareStatement(sql);
			res = pstmt.executeQuery(sql);
			
		}catch (SQLException e){
			e.printStackTrace();
		}
		
	}
	
	public void PrintResult(){
		System.out.println("------result-------");
		try {
			ResultSetMetaData resdata = res.getMetaData();
			int i;
			// 打印结果的字段
			for ( i = 0; i < resdata.getColumnCount(); i++){
				//out.println("colum count = " + resdata.getColumnCount());
				String columnname = resdata.getColumnName(i+1);
				System.out.print(columnname + "\t\t");
			}
			// 打印结果的数据（除字段外）
			out.println();
			while(res.next()){
				for (i = 0; i < resdata.getColumnCount(); i++){
					String s = res.getString(i+1);
					System.out.print(s + "\t\t");
				}
				out.println();
			}
	
		}
		catch (SQLException e){
			e.printStackTrace();
		}
	}

}</span>

Test.java 文件（程序）如下：

package SQL.Demo;

public class Test {
	
	public static void main(String[] args){
		//驱动程序

		//"jdbc:mysql://127.0.0.1:3306/scutcs";
		String url = "jdbc:mysql://localhost:3306/mydb";
		//数据的用户和密码
		String user = "andrew";
		String pwd = "andrew123";
		
		//DBHelper db1  = new DBHelper();
		DBHelper db1 = new DBHelper();
		db1.Connect(url, user, pwd);
		String sql = "Insert into test_tbl (name) values ('Andrew'); ";
		db1.Excute(sql);
		db1.Excute("Insert into test_tbl (name) values ('Ray'); ");
		db1.Excute("Insert into test_tbl (name) values ('Fenny'); ");
		db1.ExcuteQuery("select * from test_tbl");
		db1.PrintResult();
		db1.Close();

	}
	
}

运行结果如下：

注意：由于我们没有配置SSL所以，出现WARN: Establishing SSL ....

SSL 配置，请自行百度。