import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class SqlClass {

	public static final String url = "jdbc:Mysql://localhost:3306/zichenDB?characterEncoding=utf8&useSSL=true";
	public static final String name = "com.mysql.jdbc.Driver";
	public static final String user = "root";
	public static final String password = "admin123456";

	static Connection con;
	String sql;

	public void SqlClassTest() throws ClassNotFoundException {
		System.out.println("----------------------------------\n");
		System.out.println("hello, sql -- class");

		try {
			Class.forName(name);
			con = (Connection) DriverManager.getConnection(url, user, password);
			if(!con.isClosed()) {
				System.out.println("connection success");

				Statement sqlMent = (Statement) con.createStatement();
				// 同名数据库, 删除
				sqlMent.executeUpdate("drop table if exists orderStaff");
				sql = "create table orderStaff(orderId int not null, name varchar(20), orderDesc varchar(32), primary key(orderId))";
	            		int result = sqlMent.executeUpdate(sql);
	            		if(result == -1) {
	            			System.out.println("创建失败");
	            		}

				// 插入数据库
				sqlMent.executeUpdate("insert orderStaff values(1, 'zichen01', 'zichen00001')");
				sqlMent.executeUpdate("insert orderStaff values(2, 'zichen02', 'zichen00002')");
				sqlMent.executeUpdate("insert orderStaff values(3, 'zichen03', 'zichen00003')");

				// 执行查询数据
				String query = "select * from orderStaff";
				ResultSet ret = sqlMent.executeQuery(query);
				System.out.println("orderStaff 表中得数据如下:");
				System.out.println("------------------------");
				while(ret.next()) {
					int orderId = ret.getInt("orderId");
					String name = ret.getString("name");
					String desc = ret.getString("orderDesc");
					// 取得数据库中的数据
					System.out.println("id:" + orderId + " name:"+name + " desc:"+desc);
				}
				sqlMent.close();
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		System.out.println("----------------------------------\n");
	}
}