Basic Java SQL DB interface


———————————————————————————————————

Here is little code snippet to connect to; insert,delete to/from a database.

The class can be instantiated :

Process proc = new Process();

proc.init();

//This is a database that has 3 columns : emails, password , content(string)

proc.insertData(email,password,uristream)

proc.close(); // close connection when we are done

———————————————————————————————————

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

class Process{
private String table;
Connection conn;
Statement stmt;

private final String url = “jdbc:mysql://:/”;
private final String password =”password”;
private final String userName = “root”;
private final String dbName = “testdb”;

Process(){
}

//Provided when db is not hardcoded into source
Process(String dbName,String userName, String password){
initDB(dbName,userName,password);
}

void selectTable(String thistable ){
table = thistable;
}

String getTable(){
return table;
}

void initDB(String dbName,String userName, String password){
try
{
// Load the jdbc driver class
Class.forName( “com.mysql.jdbc.Driver” ).newInstance();

// Then we ask a connection from the DriverManager by passing the
// connection URL and the password.
conn = DriverManager.getConnection( url+dbName,userName,password);
stmt = conn.createStatement();
System.out.println(“connection!”);
}
catch( Exception e )
{
System.out.println(“Could not init()” );
System.out.println( e.getMessage() );
e.printStackTrace();
}
}

void initDB(){
try
{
// Load the jdbc driver class
Class.forName( “com.mysql.jdbc.Driver” ).newInstance();

// Then we ask a connection from the DriverManager by passing the
// connection URL and the password.
conn = DriverManager.getConnection( url+dbName,userName,password);
stmt = conn.createStatement();
System.out.println(“connection!”);
}
catch( Exception e )
{
System.out.println(“Could not init()” );
System.out.println( e.getMessage() );
e.printStackTrace();
}
}

//INSERT INTO table_name VALUES (value1, value2, value3,…)

void insertData(String email,String password,String uristream){

try{
String query = “INSERT INTO “+getTable()+” VALUES (?,?,?)”;
PreparedStatement stmt= conn.prepareStatement(query);
stmt.setString(1, email);
stmt.setString(2,password);
stmt.setString(3,uristream);
stmt.executeUpdate();
}
catch(SQLException e){
System.out.println( “SQLException: Unable to insert” );
System.out.println( “SQLException: ” + e.getMessage() );
System.out.println( “SQLState: ” + e.getSQLState() );
System.out.println( “VendorError: ” + e.getErrorCode() );
}

}

//DELETE FROM table_name WHERE some_column=some_value
void deleteData(String colName, String value){

// To delete records from tables we create an SQL delete command.
// The question mark that we used in the where clause will be the
// holder of value that will be assigned by PreparedStatement class
String query = “DELETE FROM “+getTable()+” WHERE “+colName+” = ?”;

// Create a statement object. We use PreparedStatement here.

try {
PreparedStatement stmt = conn.prepareStatement(query);

// Pass a value of a the variable that will tell the database which
// records in the database to be deleted. Remember that when
// using a statement object the index parameter is start from
// 1 not 0 as in the Java array data type index
stmt.setString(1,value);

// Tell the statement to execute the command. The executeUpdate()
// method for a delete command returns number of records deleted
// as the command executed in the database. If no records was
// deleted it will simply return 0
int rows = stmt.executeUpdate();

System.out.println(rows + ” record(s) deleted from “+getTable());
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println( “SQLException: Unable to delete” );
e.printStackTrace();
}

}
void closeDb(){

try {
if (conn != null && !conn.isClosed())
{
conn.close();
System.out.print(“Db connection terminated”);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}

}