package jdbc.base;
import java.sql.*;
class JdbcDemo {
private static Connection con;
private static Statement stmt;
private static PreparedStatement pstmt;
private static ResultSet rs;
private static String sql;
public static void main(String[] args) throws SQLException, ClassNotFoundException {
conectionMysql();
createTable("table3", "table2");
if (!rs.isClosed()) {
rs.close();
}
if (!stmt.isClosed()) {
stmt.close();
}
if (!con.isClosed()) {
con.close();
}
}
private static void showColumnType(String table) throws SQLException {
sql = "SELECT * FROM " + table;
PreparedStatement stmt2 = (PreparedStatement) con.prepareStatement(sql);
rs = stmt2.executeQuery(sql);
ResultSetMetaData data = rs.getMetaData();
while (rs.next()) {
for (int i = 1; i <= data.getColumnCount(); i++) {
int columnCount = data.getColumnCount();
String columnName = data.getColumnName(i);
String columnValue = rs.getString(i);
int columnType = data.getColumnType(i);
String columnTypeName = data.getColumnTypeName(i);
String catalogName = data.getCatalogName(i);
String columnClassName = data.getColumnClassName(i);
int columnDisplaySize = data.getColumnDisplaySize(i);
String columnLabel = data.getColumnLabel(i);
String schemaName = data.getSchemaName(i);
int precision = data.getPrecision(i);
int scale = data.getScale(i);
String tableName = data.getTableName(i);
boolean isAutoInctement = data.isAutoIncrement(i);
boolean isCurrency = data.isCurrency(i);
int isNullable = data.isNullable(i);
boolean isReadOnly = data.isReadOnly(i);
boolean isSearchable = data.isSearchable(i);
System.out.println(columnCount);
System.out.println("获得列" + i + "的字段名称:" + columnName);
System.out.println("获得列" + i + "的字段值:" + columnValue);
System.out.println("获得列" + i + "的类型,返回SqlType中的编号:" + columnType);
System.out.println("获得列" + i + "的数据类型名:" + columnTypeName);
System.out.println("获得列" + i + "所在的Catalog名字:" + catalogName);
System.out.println("获得列" + i + "对应数据类型的类:" + columnClassName);
System.out.println("获得列" + i + "在数据库中类型的最大字符个数:" + columnDisplaySize);
System.out.println("获得列" + i + "的默认的列的标题:" + columnLabel);
System.out.println("获得列" + i + "的模式:" + schemaName);
System.out.println("获得列" + i + "类型的精确度(类型的长度):" + precision);
System.out.println("获得列" + i + "小数点后的位数:" + scale);
System.out.println("获得列" + i + "对应的表名:" + tableName);
System.out.println("获得列" + i + "是否自动递增:" + isAutoInctement);
System.out.println("获得列" + i + "在数据库中是否为货币型:" + isCurrency);
System.out.println("获得列" + i + "是否为空:" + isNullable);
System.out.println("获得列" + i + "是否为只读:" + isReadOnly);
System.out.println("获得列" + i + "能否出现在where中:" + isSearchable);
}
}
rs.close();
stmt2.close();
}
private static void showTableDescribe(String tableName) throws SQLException {
sql = "SHOW CREATE TABLE " + tableName;
rs = stmt.executeQuery(sql);
while (rs.next()) {
for (int i = 1; i <= 2; i++) {
System.out.println(rs.getObject(i));
}
}
rs.close();
}
private static void selectValues(String tableName) throws SQLException {
sql = "SELECT * FROM " + tableName;
rs = stmt.executeQuery(sql);
System.out.println("通用遍历:");
int count = rs.getMetaData().getColumnCount();
while (rs.next()) {
for (int i = 1; i <= count; i++) {
System.out.print(rs.getString(i));
if (i < count) {
System.out.print("\t");
}
}
System.out.println();
}
}
private static void deleteValue() throws SQLException {
sql = "DELETE FROM table1 WHERE id = ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 1);
pstmt.executeUpdate();
pstmt.close();
}
private static void insertValue2() throws SQLException {
sql = "INSERT INTO table1 VALUES(?, ?);";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 100);
pstmt.setString(2, "阿基米德");
System.out.println(pstmt);
pstmt.executeUpdate();
pstmt.close();
}
private static void insertValue() throws SQLException {
sql = "INSERT INTO table2 VALUES(1, '爱因斯坦')";
int r = stmt.executeUpdate(sql);
System.out.println("影响行数:" + r);
}
private static void createTable(String... tableNames) throws SQLException {
for (String tableName : tableNames) {
sql = "create table if not exists " + tableName + "(" +
"id int comment '学号'," +
"name varchar(20) comment '名字'," +
"primary key(id, name)" +
")character set utf8, collate utf8_general_ci, comment = '用于MysqlTest类测试使用'";
System.out.println(sql);
stmt.executeUpdate(sql);
}
}
private static void dropTable(String... tableNames) throws SQLException {
for (String tableName : tableNames) {
sql = "DROP TABLE " + tableName;
System.out.println(sql);
stmt.executeUpdate(sql);
}
}
private static void searchTables() throws SQLException {
sql = "SHOW TABLES";
rs = stmt.executeQuery(sql);
System.out.println("该数据库拥有的所有表和视图:");
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
}
private static void conectionMysql() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc" +
"?useServerPrepStmts=true&cachePrepStmts=true&" +
"useUnicode=true&&" +
"characterEncoding=UTF-8", "root", "root");
if (!con.isClosed()) {
System.out.println("成功连接到MySQL数据库!");
}
stmt = con.createStatement();
}
private static void conectionOracle() throws SQLException, ClassNotFoundException {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.75.128:1521:orcl", "scott", "tiger");
if (!con.isClosed()) {
System.out.println("成功连接到Oracle数据库!");
}
stmt = con.createStatement();
}
}