JDBC 练习

import java.sql.*;
import java.util.ArrayList;


import javax.sql.*;


public class DBTest {


Connection connection;
Statement statement;
ResultSet resaultset;


public DBTest() {
super();
connection = null;
statement = null;
resaultset = null;
}


public DBTest(String url, String username, String password) {
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("驱动加载成功！");
connection = DriverManager.getConnection(url, username, password);
System.out.println("数据库连接成功！");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}


public void DBClose() {
if (connection != null)
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (statement != null)
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (resaultset != null) {
try {
resaultset.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}


public ArrayList<Hotel> getMessage(String sql) {


ArrayList<Hotel> arraylist = new ArrayList<Hotel>();
try {
statement = connection.createStatement();
resaultset = statement.executeQuery(sql);
while (resaultset.next()) {
String name = resaultset.getString(1);
String address = resaultset.getString(2);
String telephone = resaultset.getString(3);
String roomtype = resaultset.getString(4);
float roomprice = resaultset.getFloat(5);
Hotel hotel = new Hotel(name, address, telephone, roomtype,
roomprice);
arraylist.add(hotel);
}
resaultset.close();
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return arraylist;
}


public int updateMessage(String sql) {
int num;
try {
statement = connection.createStatement();
num = statement.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return -1;
}
return num;
}


public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String name = "root";
String password = "123456";
DBTest db = new DBTest(url, name, password);

Hotel h[] = new Hotel[3];
for(int i = 0 ; i<h.length ; i++) {
h[i] = new Hotel("name"+i*10,"address"+i*10,"telephone"+i*10,"roomtype"+i*10,i*10);
String sql_insert = "insert into hotel (name,address,telephone,rometype,romeprice) values("+'"'+h[i].name+'"'+","+'"'+h[i].address+'"'+","+'"'+h[i].telephone+'"'+","+'"'+h[i].rometype+'"'+","+h[i].romeprice+")";


int j = db.updateMessage(sql_insert);
System.out.println(j);
}


String sql_select = "select * from hotel";
ArrayList<Hotel> x = db.getMessage(sql_select);
for(Hotel temp : x) {
System.out.println(temp);
}


String sql_del = "delete from hotel where name = "+'"'+"asd"+'"';
int i = db.updateMessage(sql_del);
System.out.println(i);
db.DBClose();
}
}