Java DataBase(JDBC)

A brief review of Database

Database (DB):

　　an organised collection of data

　　Relational DBs (first published by Edgar F. Codd (IBM) in 1970) store data in:

　　tables A table in a database consists of:

　　rows & columns

　　　　Rows: records

　　　　Columns: attributes of the records

　　SQL:

　　Structured Query Language, The industry standard database query language (relational Database)

A brief review of Database (cont.)

　　SELECT NAME, ID FROM STUDENT WHERE GENDER=‘MALE’;

　　INSERT INTO STUDENT VALUES ( 6, ‘MARY’, ‘FEMALE’ );

　　 DELETE FROM STUDENT WHERE NAME=‘JERRY’; DROP TABLE STUDENT;

Overview of JDBC cont.

　　Java DataBase Connectivity: java.sql

　　Major components in JDBC:

　　JDBC API gives access of programming data from the Java. By using JDBC API, applications can execute SQL statements and retrieve results and updation to the database. It standardise:

　　-Way to establish connection to database

　　-Approach to initiating queries

　　-The data structure of query result

　　-It does NOT standardise SQL syntax

　　-JDBC Driver Manager is the class in JDBC API.

　　-The objects of this class can connect Java applications to a JDBC driver

 

How to use JDBC

　　Establishing a connection to DB

　　Create a Statement instance to conduct SQL statements

　　Execute queries from JDBC

　　Receive, hold and process query results from DB

　　Check meta-information of results & databases

 

1)Establish a connection

　　Creates instance of a Connection object

　　Connection conn = DriverManager.getConnection( “url”, “username”, “password”); [throws SQLException]

　　String url= "jdbc:derby: // localhost:1527/ CarDB; create=true";

return Connection object:Creates a Statement object for sending SQL statements to the database.

2)Statement

　　Statement: object that can accept and execute a string that is a SQL statement

　　Statement statement = conn.createStatement();

 

　　Executing statements:

　　statement.executeXXXXX("a SQL statement");

　　exa:

　　executeUpdate: for database modifications //数据库的修改

　　executeQuery: for database queries //数据库的查询

　　Execute DB update statement: 

　　statement.executeUpdate(“CREATE TABLE CARTABLE”);

例子：

　　public void createTable() {

　　　　 try{

　　　　Statement statement=conn.createStatement(); //创建一个新的statement

　　　　String newTable="cartable"; //tablename

　　　　statement.executeUpdate("drop table if exists "+newTable); //更新

　　　　String sqlCreateTable="CREATE TABLE "+newTable+" (ID INT, " + "MAKE VARCHAR(50), MODEL VARCHAR(50), PRICE INT )";

　　　　statement.executeUpdate(sqlCreateTable);//更新数据库

　　　　} catch (SQLException ex) {

　　　　 System.err.println("SQLException: " + ex.getMessage());

　　　　}

　　}

Query Statement //数据查询  

　　Execute DB Query statement:

　　ResultSet: an object of type ResultSet is like a cursor（光标）

　　next() advances cursor to next tuple（数组）:

　　The first time next() returns the first tuple

　　If no more tuples then next() returns FALSE

　　Accessing components of a tuple:

　　getXXX(attribute name OR column index)

 

 

　　ResultSet rs=Statement.executeQuery(“SELECT * FROM CAR”);

　　while (rs.next()) {

　　String attribute1= rs.getString(“A1”);

　　int attribute2= rs.getInt (2);

　　 }

　　More methods control the cursor:

　　 previous(): moves the cursor backwards one row.Returns true if the cursor is now positioned on a row and false if the cursor is positioned before the first row.

　　 first()/last(): moves the cursor to the first/last row

　　 afterLast(): moves the cursor to the end of this ResultSet object, just after the last row

　　 beforeFirst(): moves the cursor to the front of this ResultSet object, just before the first row

 

　　public ResultSet getQuery() { //数据库结果集的数据表

　　 ResultSet rs=null;

　　try {

　　Statement statement=mysqlConn.createStatement();

　　String sqlQuery="select model, price from car " + "where brand='toyota'“;

　　rs=statement.executeQuery(sqlQuery);

　　while( rs.next() ) {

　　String model= rs.getString ("model");

　　// String model= rs.getString (1);

　　 int price = rs.getInt(2);

　　 System.out.println(model+": $"+price);

　　}

　　System.out.println("Table created");

　　 } catch (SQLException ex) {

　　 Logger.getLogger(DBOperations.class.getName()).log(Level.SEVERE, null, ex);

　　 }

　　return(rs);

　　 }

 

 

ResultSet metadata:

　　ResultSetMetaData: objects that can hold ResultSet metadata

　　ResultSetMetaData rsmd = rs.getMetaData();

　　To get number of columns: rsmd.getColumnCount();

　　To get the table name of a given column in the result set: rsmd.getTableName(int);

　　To get data type of a given column: rsmd.getColumnType(int);

 

 

Closing:

Close ResultSet:

　　rs.close( );

Close Statement:

　　statement.close( );

Close Connection:

　　conn.close( );

A statement will be closed if the connection instance it belongs to is closed;

A resultset will be closed if the statement instance it holds results for is closed.

 

转载于:https://www.cnblogs.com/hahaccy/p/8047849.html