Monday, May 12, 2008

Java Mysql JDBC Tutorial

Here is a simple piece of code explaining Java JDBC with Mysql.

Our case is to send email to customers who have negative balance. You cannot send email to a customer more than once a day.

First we select customer list to send email , try to send their balance and update last_email_sent_date if mail sent.

Our mysql server is on localhost and port 3306 (mysql default port)
We have to grant access to required database or objects for creating a jdbc connection to server.

here is the sample Mysql Command:

grant all on customer to 'dbuser'@'localhost' identified by 'dbpass';

which gives full access on all objects on customer database. Be carefull about that !

-----------------------------------------


try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
//driver not found
e.printStackTrace();
System.exit(-1);
}

Connection con = null;
Statement stmt = null;
ResultSet rs = null;

try {
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/customer", "dbuser", "dbpass");
stmt = con.createStatement();
//get customers which you did not sent any emails today
rs = stmt
.executeQuery("select id , name , email , balance , last_mail_sent_date from customer where last_mail_sent_date < curdate()");
while (rs.next()) {
int id = rs.getInt(1);//coloumns indexes starts from 1
String name = rs.getString(2);
//you can use column name to fecth the value , but using indexes is faster
String email = rs.getString("balance");
double balance = rs.getDouble(4);
if (balance < 0.0) {
int sendMailStatus = sendMail(email, "Dear " + name
+ " your balance is " + balance
+ ".Please send some money");
if (sendMailStatus == 1) //mail is sent
//update the customer in order to not to send email again today
con.createStatement().executeUpdate(
"update customer set last_mail_sent_date = mow() where id = "
+ id);

}//if balance
}//while

} catch (SQLException e) {
e.printStackTrace();
} finally { //clean up db connections and objects
if (rs != null)
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();

}

if (stmt != null)
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}

if (con != null)
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}

}


//check the proper JDBC Driver




Mysql curdate() function returns todays date i.e. 2008-05-12

now() function returns current date and time : 2008-05-12 23:10:28

No comments: