Sunday, June 27, 2010

Checking the database

The code currently uploaded to my java project at covers only the front end of the application. I have not formalized the back end. It remains a collection of code scraps, which I use from time to time to check what is in the database, or less often to change what is in the database.

I have not formalized it because I am currently the only user. In previous incarnations of the app I have incorporated data queries, with all sorts of filters and user customization, which no one has understood how to use.

My current ad hoc query code is as follows:

public class ItemdbQuery {
public static void main(String args[])
throws ClassNotFoundException, SQLException {
/**
* These are shared variable declarations
*/
Connection connect = null;
Statement stat = null;
/**
* This is the start of the main code.
*/
System.out.println("try connection");
try {
// Load database driver
Class.forName("org.apache.derby.jdbc.ClientDriver");
// Make connection
String connn = "jdbc:derby://192.168.2.2:1527/dbAMJ";
connect = DriverManager.getConnection( connn );
stat = connect.createStatement();
System.out.println("connection succeeds");
} catch(Exception ex) {
System.out.println("connection fails");
System.err.println("SQLException: " + ex.getMessage());
}
// Execute a query
System.out.println("try query");
try {
String aquery = "SELECT * FROM Items";
ResultSet resultSet = stat.executeQuery(aquery);
// Display results
while(resultSet.next()) {
System.out.println(resultSet.getString(1) + " " +
resultSet.getString(2) + " " +
resultSet.getString(3) + " " +
resultSet.getString(4) + " " +
resultSet.getString(5) + " " +
resultSet.getString(6));
}
resultSet.close();
stat.close();
System.out.println("query succeeds");
} catch(Exception ex) {
System.out.println("query fails");
System.err.println("SQLException: " + ex.getMessage());
} finally {
// Close connection
if (connect!= null) {
try {
connect.close();
System.out.println("disconnection succeeds");
} catch (SQLException sqlEx) {
System.out.println("disconnection fails");
}
}
}
}
}

This code differs from that used for my financial transaction query in that the database is hosted remotely, so the derby "Clientdriver" was used, and the connection string included the local IP address of my server.

A sample of the data output is given below:

23 1247061594801 1 3 + 2 =   1 8.683068
24 1247061594801 1 5 + 3 = 1 36.518562
25 1247061594801 1 2 + 5 = 1 39.164494

The fields are essentially: an index, a datestamp, an operation code, an item, the raw score for the item, and the scoring rate. This can be sucked into a spreadsheet or GUI database for further analysis.

21 comments:

熙辰 said...

向著星球長驅直進的人,反比踟躕在峽路上的人,更容易達到目的。............................................................

雲亨雲亨雲亨 said...

當一個人內心能容納兩樣相互衝突的東西,這個人便開始變得有價值了。............................................................

JasonBirk佳琪 said...

Thanks a lot for sharing (o>▽<)..................................................................

吳婷婷 said...

培養健全孩子最好的方法是父母先成為健全的人。............................................................

吳婷婷 said...

Better late than never...................................................................

怡婷怡婷 said...

蛛絲馬跡皆學問、落花水面皆文章............................................................

溫緯李娟王季 said...

Quality is better than quantity.............................................................

香廷香廷 said...

做好事,不需要給人知道,雖然只是一件微不足道的事,但我相信,這會帶給我快樂。..................................................

家榮家榮 said...

大肚能容,了卻人間多少事,滿腔歡喜,笑開天下古今愁。..................................................

琬安琬安 said...

人應該做自己認為對的事,而不是一味跟著群眾的建議走。..................................................

李威昌v彥霖 said...

愛情是一位偉大的導師,教我們重新作人.................................................................

勳洪華 said...

Learning makes life sweet.

劉林育雅嘉韋 said...

一棵樹除非在春天開了花,否則難望在秋天結果。............................................................

文岳仲君 said...

生命是一頓豐富的宴席,有人卻寧可挨餓................................................

蔡曼鄭美玉屏 said...

生命是一頓豐富的宴席,有人卻寧可挨餓................................................

麗王王珠 said...

財富並非永遠的朋友,但朋友卻是永遠的財富。......................................................................

冠陳儒 said...

愛看您的新文章!加油!..................................................................

48684 said...

來逛逛blog~~跟您打聲招呼............................................................

士文婷文婷松 said...

It is never too late to learn.......................................................................

張佳穎 said...

很精彩的部落格 期待你的繼續加油..................................................

張王雅竹欣虹 said...

笑口常開~~天天開心............................................................