import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class sqlconnection {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//配置信息
//useUnicode=true&characterEncoding=utf-8 解决中文乱码
System.out.println("#####################################################");
System.out.println("############### 欢迎来到人事管理系统 ##############");
System.out.println("############### 以管理员身份进入系统 ##############");
System.out.println("#####################################################");
System.out.println("# ###### ## ##### ######################");
System.out.println("# # #### ######## #############################");
System.out.println("# ## ### ## ## ###############################");
System.out.println("# ### ## ## ## ################################");
System.out.println("# #### # ## ## ###############################");
System.out.println("# ###### ## ##### #######################");
System.out.println("#####################################################");
System.out.println("# ####### ####### ####### #### ##### #####");
System.out.println("# ##### ##### ## ##### ##### ## ##### #####");
System.out.println("# # ### # #### #### #### ##### ## #######");
System.out.println("# ## # ## ### # # ### ##### ### #######");
System.out.println("# ### ### ### ###### ### #### #### ##### #####");
System.out.println("# ####### ## ######## ## ##### ## ##### #####");
ProgressBar bar = new ProgressBar();
try {
bar.printProgress();
} catch (InterruptedException e) {
e.printStackTrace();
}
Scanner scan = new Scanner(System.in);
for (int i = 0; i < 10; i++) {
System.out.println("\n");
}
String url = "jdbc:postgresql://192.168.56.101:26000/enmotech";
System.out.println("Success to enter the system");
System.out.println("Please enter your username");
String username = scan.nextLine(); //"enmotech"
System.out.println("Please enter your password");
String password = scan.nextLine(); //"Enm0t3ch"
System.out.println("\n");
System.out.println("\n");
System.out.println("\n");
System.out.println("\n");
System.out.println("\n");
System.out.println("\n");
System.out.println("\n");
final String JDBC_DRIVER = "org.postgresql.Driver";
Class.forName(JDBC_DRIVER);
Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement spt = connection.prepareStatement("select * from client where c_id=?");
DatabaseMetaData metaData = connection.getMetaData();
Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
while (true) {
System.out.println("##########################################################");
System.out.println("##### ####### ## ## ###### ## ####### ####");
System.out.println("##### ##### ## ########## ##### ## ####### ####");
System.out.println("##### # ### # ## #### # #### ## ####### ####");
System.out.println("##### ## # ## ## ########## ## ### ## ####### ####");
System.out.println("##### ### ### ## ########## #### # ## ####### ####");
System.out.println("##### ####### ## ## ###### ### #####");//menu
System.out.println("##########################################################");
System.out.println("Enter 1 ,you will read the table you want to read");
System.out.println("Enter 2 ,you will create the table you want to create");
System.out.println("Enter 3 ,you will insert the information you want to insert");
System.out.println("Enter 4 ,you will update the information you want to update");
System.out.println("Enter 5 ,you will drop the information or the table you want to drop");
System.out.println("Enter 6 ,you will search the information in client");
System.out.println("Enter 0 ,you will quit the system");
int number = scan.nextInt();
if (number == 1) {
looking(statement, metaData);
for (int i = 0; i < 10; i++) {
System.out.println('\n');
}
}
if (number == 2) {
create(statement);
for (int i = 0; i < 10; i++) {
System.out.println('\n');
}
}
if (number == 3) {
insert(statement, metaData);
for (int i = 0; i < 10; i++) {
System.out.println('\n');
}
}
if (number == 4) {
update(statement, metaData);
for (int i = 0; i < 10; i++) {
System.out.println('\n');
}
}
if (number == 5) {
drop(statement, metaData);
for (int i = 0; i < 10; i++) {
System.out.println('\n');
}
}
if (number == 6) {
search(spt);
for (int i = 0; i < 10; i++) {
System.out.println('\n');
}
}
if (number == 0) {
break;
}
}
scan.close();
statement.close();
connection.close();
}
public static void looking(Statement statement, DatabaseMetaData Metadata) throws SQLException {
boolean flag = true;
ResultSet tables = Metadata.getTables(null, null, "%", new String[]{"TABLE"});
List<String> colNamesList = new ArrayList<String>();
List<String> TablesName = new ArrayList<String>();
while (tables.next()) {
// 列的个数
int columnCount = tables.getMetaData().getColumnCount();
//获取表名称
TablesName.add(tables.getString("TABLE_NAME"));
for (int i = 1; i <= columnCount; i++) { // 获取列名称
String columnName = tables.getMetaData().getColumnName(i);
colNamesList.add(columnName);
}
// 根据指定列名称获取数据
String TABLE_NAME = tables.getString("TABLE_NAME");
System.out.print(TABLE_NAME + "\t");
String TABLE_CAT = tables.getString("TABLE_CAT");
System.out.print(TABLE_CAT + "\t");
String TABLE_SCHEM = tables.getString("TABLE_SCHEM");
System.out.print(TABLE_SCHEM + "\t");
String TABLE_TYPE = tables.getString("TABLE_TYPE");
System.out.print(TABLE_TYPE + "\t");
String TYPE_SCHEM = tables.getString("TYPE_SCHEM");
System.out.print(TYPE_SCHEM + "\t");
String TYPE_NAME = tables.getString("TYPE_NAME");
System.out.println(TYPE_NAME + "\t");
}
Scanner scan = new Scanner(System.in);
String str = scan.next();
String sql = "select * from " + str;
while (flag) {
List<String> ValuesName = new ArrayList<String>();
if (TablesName.contains(str)) {
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
int col = rs.getMetaData().getColumnCount();
for (int i = 1; i <= col; i++) {
String valuename = rs.getMetaData().getColumnName(i);
ValuesName.add(valuename);
}
for (int i = 0; i < col; i++) {
System.out.print(ValuesName.get(i) + ":" + rs.getObject(ValuesName.get(i)) + "\t");
}
System.out.println(ValuesName.get(col - 1) + ":" + rs.getObject(ValuesName.get(col - 1)) + "\t");
}
System.out.println("table success search,please choose your choice to decide your next event");
System.out.println("enter 1 you will quit");
System.out.println("enter 0 you will research");
int choice = scan.nextInt();
if (choice == 1) {
flag = false;
} else {
System.out.println("input table name");
str = scan.next();
sql = "select * from " + str;
}
} else {
System.out.println("table not exist,please choose your choice");
System.out.println("enter 1 you will quit");
System.out.println("enter 0 you will research");
int choice = scan.nextInt();
if (choice == 1) {
flag = false;
} else {
System.out.println("input table name");
str = scan.next();
sql = "select * from " + str;
}
}
}
System.out.println("return");
ProgressBar bar = new ProgressBar();
try {
bar.printProgress();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
public static void create(Statement statement) throws SQLException {
System.out.println("please enter the sql to create the table you want to create");
Scanner scan = new Scanner(System.in);
String q = "";
while (scan.hasNextLine()) {
String wording1 = scan.nextLine();
if (wording1.equals("quit")) {
break;
} else {
q = q + " " + wording1;
}
}
System.out.println(q);
statement.executeUpdate(q);
System.out.println("Created table in given database...");
System.out.println("return menu");
ProgressBar bar = new ProgressBar();
try {
bar.printProgress();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
public static void insert(Statement statement, DatabaseMetaData Metadata) throws SQLException {
boolean flag = true;
ResultSet tables = Metadata.getTables(null, null, "%", new String[]{"TABLE"});
List<String> colNamesList = new ArrayList<String>();
List<String> TablesName = new ArrayList<String>();
while (tables.next()) {
// 列的个数
int columnCount = tables.getMetaData().getColumnCount();
//获取表名称
TablesName.add(tables.getString("TABLE_NAME"));
for (int i = 1; i <= columnCount; i++) { // 获取列名称
String columnName = tables.getMetaData().getColumnName(i);
colNamesList.add(columnName);
}
// 根据指定列名称获取数据
String TABLE_NAME = tables.getString("TABLE_NAME");
System.out.print(TABLE_NAME + "\t");
}
Scanner scan = new Scanner(System.in);
System.out.println("\ninput the table name that you want to operate");
String table = scan.next();
String sql = "select * from " + table;
ResultSet rs = statement.executeQuery(sql);
while (flag) {
List<String> ValuesName = new ArrayList<String>();
List<String> ValuesType = new ArrayList<String>();
if (TablesName.contains(table)) {
while (rs.next()) {
int col = rs.getMetaData().getColumnCount();
for (int i = 1; i <= col; i++) {
String valuename = rs.getMetaData().getColumnName(i);
String valuetype = rs.getMetaData().getColumnTypeName(i);
ValuesName.add(valuename);
ValuesType.add(valuetype);
}
for (int i = 0; i < col; i++) {
System.out.print(ValuesName.get(i) + "\t");
}
System.out.println(ValuesName.get(col - 1) + "\t");
for (int i = 0; i < col; i++) {
System.out.print(ValuesType.get(i) + "\t");
}
System.out.println(ValuesType.get(col - 1) + "\t");
}
System.out.println("please enter the sql to insert the information you want to insert");
Scanner input = new Scanner(System.in);
String sqlline = input.nextLine();
statement.executeUpdate(sqlline);
System.out.println(sqlline);
System.out.println("finish inserting information");
System.out.println("enter 1 you will quit");
System.out.println("enter 0 you will research");
int choice = scan.nextInt();
if (choice == 1) {
flag = false;
} else {
System.out.println("input the table name that you want to operate");
table = scan.next();
sql = "select * from " + table;
rs = statement.executeQuery(sql);
}
} else {
System.out.println("table not exist");
System.out.println("enter 1 you will quit");
System.out.println("enter 0 you will insert again");
int choice = scan.nextInt();
if (choice == 1) {
flag = false;
} else {
System.out.println("input the table name that you want to operate");
table = scan.next();
sql = "select * from " + table;
rs = statement.executeQuery(sql);
}
}
}
}
public static void update(Statement statement, DatabaseMetaData Metadata) throws SQLException {
looking(statement, Metadata);
System.out.println("\nplease enter the sql to update the information you want to update");
Scanner scan = new Scanner(System.in);
String sql = scan.nextLine();
statement.executeUpdate(sql);
System.out.println("return menu");
ProgressBar bar = new ProgressBar();
try {
bar.printProgress();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
public static void drop(Statement statement, DatabaseMetaData Metadata) throws SQLException {
boolean flag = true;
String sql;
System.out.println("please select table");
looking(statement, Metadata);
while(flag){
Scanner scan = new Scanner(System.in);
System.out.println("enter 1,delete table,enter 0 delete information");
int choice = scan.nextInt();
if (choice == 0){
System.out.println("input table name");
String table = scan.next();
System.out.println("input key");
String name = scan.next();
System.out.println("input key");
if(!name.equals("id") && !name.equals("c_id")){
String id = scan.next();
sql = "delete from "+table+" "+ "where "+name+" "+ "=" + id;
}else{
int id = scan.nextInt();
sql = "delete from "+table+" "+ "where "+name+" "+ "=" + id;
}
statement.executeUpdate(sql);
}else{
System.out.println("input table name");
String table = scan.next();
sql = "drop table "+ table;
statement.executeUpdate(sql);
}
System.out.println("enter 1 you will quit");
System.out.println("enter 0 you will continue");
int choose = scan.nextInt();
if(choose == 1){
flag = false;
}else{
continue;
}
}
}
public static void search(PreparedStatement preparedStatement) throws SQLException {
boolean flag = true;
System.out.println("please enter the c_id");
Scanner scan = new Scanner(System.in);
int temp = scan.nextInt();
preparedStatement.setInt(1, temp);//设置条件id
ResultSet rs = preparedStatement.executeQuery();
while (flag) {
while (rs.next()) {
System.out.print("c_id:" + rs.getObject("c_id") + "\t");
System.out.print("c_name:" + rs.getObject("c_name") + "\t");
System.out.print("c_mail:" + rs.getObject("c_mail") + "\t");
System.out.print("c_id_card:" + rs.getObject("c_id_card") + "\t");
System.out.print("c_phone:" + rs.getObject("c_phone") + "\t");
System.out.println("c_password:" + rs.getObject("c_password") + "\t");
}
System.out.println("Success search");
System.out.println("enter 1 you will quit");
System.out.println("enter 0 you will research");
int choice = scan.nextInt();
if (choice == 1) {
flag = false;
}else {
temp = scan.nextInt();
preparedStatement.setInt(1, temp);
rs = preparedStatement.executeQuery();
}
}
}
}
class ProgressBar {
private int index = 0;
private String finish;
private String unFinish;
// 进度条粒度
private final int PROGRESS_SIZE = 50;
private int BITE = 2;
private String getNChar(int num, char ch){
StringBuilder builder = new StringBuilder();
for(int i = 0; i < num; i++){
builder.append(ch);
}
return builder.toString();
}
public void printProgress() throws InterruptedException {
System.out.print("Progress:");
finish = getNChar(index / BITE, '█');
unFinish = getNChar(PROGRESS_SIZE - index / BITE, '─');
String target = String.format("%3d%%[%s%s]", index, finish, unFinish);
System.out.print(target);
while (index <= 100){
finish = getNChar(index / BITE, '█');
unFinish = getNChar(PROGRESS_SIZE - index / BITE, '─');
target = String.format("%3d%%├%s%s┤", index, finish, unFinish);
System.out.print(getNChar(PROGRESS_SIZE + 6, '\b'));
System.out.print(target);
Thread.sleep(50);
index++;
}
}
}
进度条代码借鉴网络大神,特此敬谢