Programmer's Blog

Programmer's reference

Category Archives: JDBC

[JDBC] Prepared Statement with HashMap Example

//for demonstration, try catch phrases are removed

public void updateCoffeeSales(HashMap<String, Integer> salesForWeek) {

    PreparedStatement updateSales = null;
    String updateString = "update " + dbName + ".COFFEES " +
                          "set SALES = ? where COF_NAME = ?";

    con.setAutoCommit(false);    //con is java.sql.jdbc.Connection
    updateSales = con.prepareStatement(updateString);

    //Map fields <COF_NAME, SALES>
    for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
            updateSales.setInt(1, e.getValue().intValue());  //SALES
            updateSales.setString(2, e.getKey());   //COF_NAME
            updateSales.executeUpdate();
            con.commit();
     }

     if (updateSales != null) {
          updateSales.close();
     }
     con.setAutoCommit(true);
}
Advertisements

[JDBC] Simple Query Statement

Complete Example for simple statement, note that it is using com.mysql.jdbc Connections from mysql-connector-java (Connector/J)

Configuring class path in windows if you install using a MSI, use the following path:

C:\Progra~2\MySQL\MySQL~1  , where Progra~2 refers to Program Files (x86)

package com.testing.testing;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import com.mysql.jdbc.Statement;
import com.mysql.jdbc.Connection;

public class Testing {
    
        public void getData() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {

            Class.forName("com.mysql.jdbc.Driver");
            
            com.mysql.jdbc.Connection conn = null;
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "user", "passwd");
                
            System.out.println("Connected to database");

            //Begin Statement---------------------------------
            Statement stmt = (Statement) conn.createStatement();
            String query = " Select * from mytable";
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next()) {
                
                int id = rs.getInt("ID");
                String name = rs.getString("NAME");
                System.out.println("Record: " + id + " " + name);
            }            
           //End Statement---------------------------------
           conn.close();
}

        public static void main(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException {
            
            Testing mytest = new Testing();
            try {        
                mytest.getData();
            } catch (Exception e) 
            {
                System.out.println("SQL ERROR!");
                e.printStackTrace();
            }
        }
}

[JDBC] Connection to MySQL

//mysql-connector-java library is needed
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import com.mysql.jdbc.Statement;
import com.mysql.jdbc.Connection;
public class myclass{

    public Connection getConnection() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {

            Class.forName("com.mysql.jdbc.Driver").newInstance();
            com.mysql.jdbc.Connection conn = null;
         
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname", "user", "passwd");
                
            System.out.println("Connected to database");
            return conn;
        }
    ...
    ... conn.close();
}


/* For setting parametered login, use Properties object

            Properties connectionProps = new Properties();
            connectionProps.put("user", varUser);
            connectionProps.put("password", varPasswd);
            ...
            conn = (Connection) DriverManager.getConnection(varURL, connectionProps);

*/