package DatabaseTest;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
public class FunctionToDatabase {
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String paramFile)throws Exception{
Properties props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public void work() throws Exception{
Class.forName(driver);
}
public void set(String sql)throws Exception{
this.work();
try(
Connection conn = DriverManager.getConnection(url,user,pass);
Statement stmt = conn.createStatement())
{
stmt.execute(sql);
}
}
public void switchFunction() throws Exception{
Scanner sc = new Scanner(System.in);
System.out.println("Please input your select 1-5:");
System.out.println("************************************");
System.out.println("1---add");
System.out.println("2---delete");
System.out.println("3---alter");
System.out.println("4---search");
System.out.println("5---exit system");
System.out.println("************************************");
System.out.println("Now! Please select your choice to manage the database!");
switch(sc.nextInt()){
case 1://add
add();
switchFunction();
break;
case 2://delete
delete();
switchFunction();
break;
case 3://Alter
alter();
switchFunction();
break;
case 4://Search
search();
switchFunction();
break;
case 5://exit
System.exit(0);
break;
default:
System.out.println("------- input error,try it again ! -------");
switchFunction();
break;
}
}
//add data to database
public void add(){
Scanner sc = new Scanner(System.in);
try{
this.work();
Connection conn = DriverManager.getConnection(url,user,pass);
PreparedStatement pstmt = conn.prepareStatement("insert into " +
"ConnectTest values(?,?,?,?);");
// name age number money
System.out.println("Please input your times you want to insert:");
int cout = sc.nextInt();
System.out.println("please input the data:");
for (int j = 0; j < cout; j++){
System.out.println("name" + (j+1) + ":");
String Tname = sc.next();
System.out.println("age" + (j+1) + ":");
int Tage = sc.nextInt();
System.out.println("number" + (j+1) + ":");
int Tnum = sc.nextInt();
System.out.println("money" + (j+1) + ":");
double Tmoney = sc.nextDouble();
pstmt.setString(1,Tname);
pstmt.setInt(2,Tage);
pstmt.setInt(3,Tnum);
pstmt.setDouble(4,Tmoney);
pstmt.executeUpdate();
}
}
catch(Exception e){
e.printStackTrace();
}
System.out.println("insert sucess!");
}
//delete data in database
//delete from
public void delete() throws Exception{
Scanner sc = new Scanner(System.in);
this.work();
try(
Connection conn = DriverManager.getConnection(url,user,pass);
Statement stmt = conn.createStatement();
PreparedStatement pstmt = conn.prepareStatement("delete from connecttest "
+ "where number = ?;");
){
System.out.println("Please input the number you want to delete:");
int num = sc.nextInt();
pstmt.setInt(1, num);
pstmt.executeUpdate();
System.out.println("delete sucess!");
}
}
//alter data in database
//modify money
public void alter() throws Exception{
Scanner sc = new Scanner(System.in);
this.work();
try(
Connection conn = DriverManager.getConnection(url,user,pass);
PreparedStatement pstmt = conn.prepareStatement("update ConnectTest " +
"set money = ?" +
"where number = ?;");
){
System.out.println("Please input the number you want to alter:");
Thread.sleep(1000);
int Tnum = sc.nextInt();
System.out.println("Please input your money:");
double Tmoney = sc.nextDouble();
pstmt.setDouble(1, Tmoney);
pstmt.setInt(2, Tnum);
pstmt.executeUpdate();
System.out.println("alter sucess!");
}
}
//find the data by index
public void search() throws Exception{
Scanner sc = new Scanner(System.in);
this.work();
try(
Connection conn = DriverManager.getConnection(url,user,pass);
PreparedStatement pstmt = conn.prepareStatement("select from cnnnecttest" +
"where number = ?;");
ResultSet rs = pstmt.executeQuery();
){
System.out.println("Please input the number you want to search:");
int Tnum = sc.nextInt();
pstmt.setInt(1,Tnum);
System.out.println(rs.getString(1) + ' ' + rs.getString(2));
}
System.out.println("search sucess!");
}
public void drop(String sql) throws Exception{
this.work();
try(
Connection conn = DriverManager.getConnection(url,user,pass);
Statement stmt = conn.createStatement()
)
{
stmt.executeUpdate(sql);
}
}
public static void main(String[] args) throws Exception {
FunctionToDatabase ftd = new FunctionToDatabase();
ftd.initParam("mysql.ini");
//ΪʲôһŜcreate databaseÿÿÿ
//ftd.drop("drop database DatabaseTest");
//ftd.set("create database DatabaseTest;");
ftd.drop("drop table if exists ConnectTest;");
ftd.set("create table ConnectTest (name varchar(255) not null,"
+ "age int not null, " + " number int(11) primary key,"
+ " money double not null);");
System.out.println("Welcom!");
ftd.switchFunction();
//ftd.drop("drop table ConnectTest;");
//ftd.drop("drop database DatabaseTest");
}
}