15-Laboratoriya mashg`uloti

statement.execute(“SQL so`rov qatori”) bilan natijasi massivga olinmaydigan so`rovlar (Insert, Update, Delete) bajariladi

ResultSet rs = statement.execute(“SQL so`rov qatori”) bilan bajarilgan so`rov natijasi ko`rsatilgan massivga (rs) yoziladi.

conn.close() – conn nomli bog`lanishni yopadi.

Turli MBga bog`lanishni amalga oshirish namunalari.


MySQLga ulanish:
Class.forName("com.mysql.jdbc.Driver");
Connection conn =
DriverManager.getConnection("jdbc:mysql://hostname:port/dbname",
"username", "password");
conn.close();


PostgreSQLga ulanish:
Class.forName("org.postgresql.Driver");
Connection connection =
DriverManager.getConnection("jdbc:postgresql://hostname:port/dbname",
"username", "password");
connection.close();


ORACLEga ulanish:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:mkyong",
"username","password");
connection.close();

Iloji boricha JDBC drayverini try{} va catch{} ichiga joylashtirish lozim. Bu esa drayverning kompyuterda borligini va ishlashini nazorat qiladi.

try
{
Class.forName("com.mysql.jdbc.Driver"); }
catch (ClassNotFoundException e)
{
System.out.println("Where is your MySQL JDBC Driver?");
e.printStackTrace();
return;
}

Javada MBga ulanish uchun klass hosil qilinadi:

private static Connection getDBConnection()
{
Connection dbConnection = null;
try {Class.forName(DB_DRIVER);}
catch (ClassNotFoundException e) {System.out.println(e.getMessage());}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION,
DB_USER,DB_PASSWORD);
return dbConnection;
}
catch (SQLException e) {System.out.println(e.getMessage());}
return dbConnection;
}
Quyida ko`rsatilgan MBda jadval yaratish metodi keltirilgan:
private static void createDbUserTable() throws SQLException{
Connection dbConnection = null;
Statement statement = null;
String createTableSQL = "
CREATE TABLE DBUSER("
+ "USER_ID NUMBER(5) NOT NULL, "
+ "USERNAME VARCHAR(20) NOT NULL, "
+ "CREATED_BY VARCHAR(20) NOT NULL, "
+ "CREATED_DATE DATE NOT NULL, "
+ "PRIMARY KEY (USER_ID) " + ")";
try {
dbConnection = getDBConnection();
statement = dbConnection.createStatement();
// SQL so`rovini bajarish
statement.execute(createTableSQL);
System.out.println("Table \"dbuser\" is created!");
}
catch (SQLException e) {System.out.println(e.getMessage());}
finally {
if (statement != null) {statement.close();}
if (dbConnection != null) {dbConnection.close();}
}
}
//main funksiyasi ichiga createDbTable() metodini chaqirish
public static void main(String[] argv) {
try {createDbUserTable();}
catch (SQLException e) {System.out.println(e.getMessage());}
}

MBga ma`lumot kiritish va o`qish:

String insertTableSQL = "INSERT INTO DBUSER"
+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) "
+ "VALUES"+ "(1,`mkyong`,`system`, "
+ "to_date(‘"+ getCurrentTimeStamp() + "‘, ‘yyyy/mm/dd hh24:mi:ss`))";
private static String getCurrentTimeStamp()
{Date today = new Date();
return dateFormat.format(today.getTime()); }
statement.executeUpdate(insertTableSQL);
//MBdan ma`lumotlarni o`qish:
String selectTableSQL = "SELECT USER_ID, USERNAME from DBUSER";
try {
dbConnection = getDBConnection();
statement = dbConnection.createStatement();
// MBdan ma`lumotlarni tanlash
ResultSet rs = statement.executeQuery(selectTableSQL);
// Ma`lumot olingan bo`lsa while tsikliga tashlash.
while (rs.next()) {
String userid = rs.getString("USER_ID");
String username = rs.getString("USERNAME");
System.out.println("userid: " + userid);
System.out.println("username: " + username); }
}
catch (SQLException e) {System.out.println(e.getMessage());}