package com.woniuxy.java.io;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class JDBCReadWrite {

// 声明数据库驱动程序类

private static String driverClassName = "com.mysql.jdbc.Driver";

// 定义数据库连接字符串

private static String url = "jdbc:mysql://localhost:3306/learn?"

+ "user=root&password=&useUnicode=true&characterEncoding=UTF8";

public static void main(String[] args) throws Exception {

JDBCReadWrite jdbc = new JDBCReadWrite();

// jdbc.insert();

// jdbc.getCount();

// jdbc.update();

// jdbc.delete();

// jdbc.select();

// 从二维数组中遍历整个数据集的记录

String[][] data = jdbc.getAllData();

for (int i=0; i<data.length; i++) {

for (int j=0; j<data[i].length; j++) {

System.out.println(data[i][j]);

}

             System.out.println("===================");

}

}

// 建议与数据库之间的连接

public Connection getConnection() throws Exception {

Connection conn = null;

Class.forName(driverClassName).newInstance();

// new com.mysql.jdbc.Driver();   也可以直接这样来实例化

conn = DriverManager.getConnection(url);

return conn;

}

// 执行简单的SQL查询语句，了解其运行过程

public void getCount() throws Exception {

Statement stmt = this.getConnection().createStatement();

String sql = "select count(*) from user";

ResultSet rs = stmt.executeQuery(sql);

rs.next(); // 让结果集定位到第一行

System.out.println(rs.getInt(1));   // 获取第1列的结果

}

// 往数据库中插入一条新的记录

public void insert() throws Exception {

Statement stmt = this.getConnection().createStatement();

String sql = "insert into user values(0,'qiang',"

+ "'e10adc3949ba59abbe56e057f20f883e','18812345678','qiang@woniuxy.com')";

stmt.executeUpdate(sql);

}

// 遍历整个表的数据

public void select() throws Exception {

String sql = "select * from user";

Statement stmt = this.getConnection().createStatement();

ResultSet rs = stmt.executeQuery(sql);

while(rs.next()) {

System.out.print(rs.getInt("id") + "\t");

System.out.print(rs.getString("username") + "\t");

System.out.println(rs.getString("phone"));

}

}

// 执行update语句实现更新

public void update() throws Exception {

String sql = "update user set phone='18509876543' where id=3";

Statement stmt = this.getConnection().createStatement();

stmt.executeUpdate(sql);

}

// 执行delete语句实现删除

public void delete() throws Exception {

String sql = "delete from user where id=3";

Statement stmt = this.getConnection().createStatement();

stmt.executeUpdate(sql);

}

// 将整个数组表的内容保存到二维数组中

public String[][] getAllData() throws Exception {

String sql = "select * from user";

PreparedStatement ps = this.getConnection().prepareStatement(sql);

ResultSet rs = ps.executeQuery();

// 定义元数据对象以获得数据库中的列，以此来定义二维数组的长度

ResultSetMetaData metaData = ps.getMetaData();

int columnCount = metaData.getColumnCount();

int rowCount = 0;

// 循环遍历该结果集，以此来获取表的行数量，用以定义二维数组的长度

while(rs.next()) {

rowCount ++;

}

rs.first(); // 将当前记录游标移动到第一行

String[][] data = new String[rowCount][columnCount];

int row = 0;

// 遍历整个结果集，将将每一条数据保存到二维数组中

// 由于游标已经在第一行，所以此处使用do...while循环，否则会路过第一行内容

do {

for (int col=0; col<columnCount; col++) {

// 从结果集中取值的下标从1开始，而不是0

data[row][col] = rs.getString(col+1);

}

row++;

} while (rs.next());

return data;

}

}

// 使用PreparedStatement

public void psInsert() throws Exception {

String sql = "insert into user(username,password,phone,email) values(?,?,?,?)";

PreparedStatement ps = this.getConnection().prepareStatement(sql);

ps.setString(1, "denny");

ps.setString(2, "c33367701511b4f6020ec61ded352059");

ps.setString(3, "15501018089");

ps.setString(4, "denn@woniuxy.com");

ps.executeUpdate();

}