database connection - wordpress.com · create a new database • create a new database “testdb”...
TRANSCRIPT
CSC 308 2.0
System Development with Java
Budditha Hettige
Department of Statistics and Computer Science
1
Database Connection
Budditha Hettige
From database to Java
• There are many brands of database: – Microsoft Access – part of Microsoft Office
– MySQL – very good, simple, open source
– Microsoft SQL Server – robust and complex
– Oracle – very robust and complex
– IBM DB2 – enterprise robust and complex
• MySQL Database
– world's most popular open source database
– http://www.mysql.com/
Budditha Hettige 2
Wamp Server for MySQL
• is an open source project, free to use (GPL licence)
• Allows you to create Web applications with Apache,
PHP, and the MySQL database
• Manage your Apache and MySQL services
• Download from
– www.wampserver.com/en/
• Tools
– PhpMyAdmin
Budditha Hettige 3
Create a new Database
• Create a new database “testdb”
• Create a new table “student”
• Create table structure
Budditha Hettige 4
Create table Structure
• Fields
– Id (Int, Primary)
– Name (varchar 25)
– Age (int)
Budditha Hettige 5
Java Database Connectivity
(JDBC)
• Sun Microsystems provided a common API to
access a data source from a Java application.
• This is called the Java Database Connectivity
(JDBC)
• JDBC is a set of classes and interfaces
• Download
– http://dev.mysql.com/get/Downloads/Connector-J/mysql-
connector-java-5.1.6.tar.gz
Budditha Hettige 6
Java Database Connectivity
• Architecture
JAVA Application
JDBC Driver Manager
JDBC
Driver1
JDBC-ODBC Bridge
ODBC Driver
JDBC
Driver2
Data
Source1 Data
Source2
Data
Source3
Budditha Hettige 7
JDBC API…
• Import the java.sql package… – import java.sql.*;
• Firstly, you need to load the driver… – Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").
newInstance(); // loading the JDBC-ODBC bridge
• Then create a connection to the data source – String url = “jdbc:odbc:dbConn”
– Connection c = DriverManager.getConnection(url, “user", “password");
Budditha Hettige 8
Sample Codes
• Create new MySQL Connection
• Insert new record
• Delete a record
• Update new record
• Show existing records
Budditha Hettige 9
Database Connection
// Manual connection for the localHost
public Connection getConnection() throws Exception
{
Connection conn = null;
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/testdb";
String username = “root";
String password = "";
Class.forName(driver); // load MySQL driver
conn = DriverManager.getConnection(url, username, password);
return conn;
}
Budditha Hettige 10
Insert new Record
public int InsertNewRecord(String name, int Age) throws SQLException
{
Statement stmt = null;
ResultSet rs = null;
int rowCount = -1;
Connection MySqlConn = null;
try {
MySqlConn = getConnection();
stmt = MySqlConn.createStatement();
rowCount = stmt.executeUpdate("insert student(name, age) values('" + name + "', " +Age + ")");
if (rowCount == 1) {
System.out.println("new row insert");
} else {
System.out.println("Not insert");
}
} catch (Exception ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
stmt.close();
return rowCount;
}
Budditha Hettige 11
Show Records
public void ReadAllRecords()
{
try {
Statement stmt = null;
ResultSet rs = null;
Connection MySqlConn = null;
int age;
String name;
MySqlConn = getConnection();
stmt = MySqlConn.createStatement();
rs = stmt.executeQuery("SELECT * FROM student");
while (rs.next())
{
age = rs.getInt("Age");
name = rs.getString("name");
System.out.println(name + ", " + age);
}
rs.close();
stmt.close();
MySqlConn.close();
} catch (Exception ex)
{
Logger.getLogger(MySQLConnection.class.getName()).log(Level.SEVERE, null, ex);
}
}
Budditha Hettige 12
Delete Records
public void DeleteRows(String stuName)
{
Statement stmt = null;
ResultSet rs = null;
Connection MySqlConn;
int rowCount = -1;
try {
MySqlConn = getConnection();
stmt = MySqlConn.createStatement();
int delete = stmt.executeUpdate("DELETE FROM student WHERE name ='"+ stuName + "'");
if(delete == 1)
{
System.out.println("Row is deleted.");
}
else
{
System.out.println("Row is not deleted.");
}
stmt.close();
} catch (Exception ex) {
Logger.getLogger(MySQLConnection.class.getName()).log(Level.SEVERE, null, ex);
} finally {
}
Budditha Hettige 13
Update Records
public void updateAge(String name, int newAge )
{
try {
boolean result = false;
Connection MySqlConn;
Statement stmt = null;
MySqlConn = getConnection();
String sql = "UPDATE student SET age = ? WHERE name = ?";
PreparedStatement prest = MySqlConn.prepareStatement(sql);
prest.setInt(1, newAge);
prest.setString(2, name);
int executeUpdate = prest.executeUpdate();
if (executeUpdate > 0) {
System.out.println("Record Updaqted");
}
MySqlConn.close();
} catch (Exception ex) {
Logger.getLogger(MySQLConnection.class.getName()).log(Level.SEVERE, null, ex);
}
}
Budditha Hettige 14
Sample Program
public static void main(String[] args) {
try {
Connection MySqlConn;
MySQLConnection con = new MySQLConnection();
// con.getConnection();
MySqlConn = con.getConnection();
con.InsertNewRecord("budditha", 34);
con.InsertNewRecord("saman", 25);
con.InsertNewRecord("kamal", 23);
con.ReadAllRecords();
con.DeleteRows("saman");
con.updateAge("budditha", 35 );
con.ReadAllRecords();
} catch (Exception ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
}
Budditha Hettige 15