PDC（四）：JDBC (Java Database Connectivity)

JDBC（Java Database Connectivity）

a) Add code to the following code fragment to:

(1) establish a connection with the ClubDB database;

(2) send the SQL statement, which is specified insqlNewTable, to the database.

try {
String url = "jdbc:derby://localhost:1527/ClubDB";
String username = "pdc";
String password=”pdc”;
String sqlNewTable="CREATE TABLE ClubMember(ID INT, NAME VARCHAR(20)";

      //add your code here
}catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());

}

这道题目第一小题的要求是让我们先和ClubDB 数据库建立联系，那就直接一点

Connection conn = DriverManager.getConnection("url","username","password");

这就是直接建立了一个联系，url、username、password都是代码中给出的内容

第二小题的要求是将在sqlnewtable中指定的SQL语句发送到数据库，那就说明数据库的内容会发生变化，那就要更新数据库

Statement statement = conn.createStatement();

statement.executeUpdate(sqlNewTable);

 

b)The CAR table below contains 4 attributes and 4 records. The data types of the 4 attributes are: ID: int, MAKE: varchar, MODEL: varchar, PRICE: int, respectively. Please add code to the getQueryResult method, so that we get the MODEL and PRICE of all TOYOTA cars, and print out the retrieved information. (The query statement specified in sqlQuery is for retrieving records of Toyota cars from the table.)

这道题要求得到MODEL and PRICE of all TOYOTA cars（所有丰田汽车的型号和价格，并打印出检索到的信息）

这里要用到的是查询语句

rs = statement.executeQuery(sqlQuery);

while(rs.next()){
String MODEL = rs.getString(1);

Int PRICE  = rs.getInt(2);

System.out.println(MODEL  + ":$" + PRICE);

}

 

c)Please explain the difference between a statement and a prepared statement in JDBC.

Statement has to compile once to execute an sql, and PrepareStatement only compiles once.

 

d)In the following code fragment, SQL statements will be sent and executed to the database management system for 3 times. Please modify the code fragment, so that we can put the 3 statements into a batch and execute them at one time.

statement.executeUpdate("SQL statement 1");

statement.executeUpdate("SQL statement 2");

statement.executeUpdate("SQL statement 3");

 

modify to :

statement.addbatch(""SQL statement 1");

statement.addbatch("SQL statement 2");

statement.addbatch("SQL statement 3");

statement.executebatch();