Przejdź do głównej zawartości

Java Database Connection: JDBC and MySQL

Java Database Connection: JDBC and MySQL

 JDBC - standard API allow to connect with MySQL

Java Application <--------- >JDBC <---------> Database

JDBC Architecture
  • JDBC Driver 
  • JDBC Driver Implementation
  • JDBC Driver Manager
JDBC API is definded in following packages:
  • java.sql and javax.sql

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

Submitting SQL Queries  - JAVA
  1. Get a connection to database
  2. Create Statement object
  3. Execute SQL query
  4. Process Result Set

Get a connection to database

                   import java.sql.*;
                    ....
                   String dbUrl = "jdbc:mysql://localhost:3306/demo?                                                                                  autoReconnect=true&useSSL=false";
                    String user = "root";
                    String pass = "Sasanka01";
                     ....
                     Connection myConn = DriverManager.getConnection(dbUrl, user, pass);

Create Statement object 

 Statement myStmt = myConn.createStatement()

Execute SQL query

                        ResultsSet myRs = myStmt.executeQuery("select * from employees");

Process Result Set
  • Result Set is initially placed before first row
  • Method: ResultSet.next()
    • moves forward one row
    • returns true if there are more rows to proces
  • Collection of methods for reading data
    • getXXX(columnName)
    • getXXX(columnIndex) one -based

while (myRs.next()) {
System.out.println(myRs.getString("last_name") + ", " + myRs.getString("first_name"));
}


Etykiety:

Komentarze

Prześlij komentarz

Popularne posty z tego bloga

SQL - Podstawy

SELECT * FROM branch; CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... ); Typy danych w kolumnach Text data types: VARCHAR (40) - string z ograniczoną liczbą znaków TEXT - string BLOB - Binary Larg Objects - 65000 bytes of data ENUM('X','Y','Z') - list of values in order we enter them SET - values are ENUM up to 64 list items    Number data types: INT (size) - integer FLOAT(size,d) - small number with floating decimal point, d - decimal point DOUBLE(size,d) large number with floating decimal point Date data types: DATE() - YYYY-MM-DD TIMESTAMP() TIME() SQL Keywords: ADD - add column ALTER - add delete modify columns in table or change data types of column in table CREATE - create database, table, index, view, procedure CREATE DATABASE CREATE TABLE CREATE INDEX CREATE VIES DELETE - delete rows from table DESC - sort results descending order DROP - delet...
Prześlij komentarz
Czytaj więcej

Java -Arrays

Array - data structure that allows us to store multiple values of the same type into a single variable Default value - zero Arrays are zero indexed - Array with ten elements will be indexed from 0 to 9 Array[] How to create array: int[] array = new int[5]; (boxes have value zero - secured slot in memory to hold values) int[] myArray = {1,2,3,4,5}; this type of initializing an array is also known as an anonymous array boolean type arrays - default value is false Errors while working with arrays ArrayIndexOutOfBoundsExeption int[] my Array = {10,35,45,78,55} myArray[5] = 55; out of bound index 5 does not exist int[] myArray = {10,15,20,17,18}; for (int i=1 ; i < myArray.lenght; i++)  System.out.println("value= " + myArray[i] it will return values from index 1 How to print out simple int array? example: int [] myArray = new int [ 2 ] ; myArray[ 0 ]= 0 ; myArray[ 1 ]= 1 ; System. out .println( "My array: " + ...
Prześlij komentarz
Czytaj więcej

Stored Procedures - JDBC Java SQL

Stored Procedures: group of SQL statements that perform a particular task  Normally created by DBA Can have any combination of input, output, and input/output parameters Benefits: Performance  - compiled once but executable more times Productivity and Ease of Use  - avoid redundant code, extend SQL Database functionality  Scalability   Maintainability Interoperability Replication Security To call stored procedure from Java The JDBC API provides the CallableStatement CallableStatement myCall = myConn.prepareCall("{ call some_stored_procedure() }"); ... myCall.execute(); JDBC API parameter types IN default INOUT OUT Stored Procedure can return result sets EXAMPLE: Created stored procedure on MySQL side - NAME:  increase_salaries_for_department DELIMITER $$ DROP PROCEDURE IF EXISTS ` increase_salaries_for_department `$$ CREATE DEFINER=`student`@`localhost` PROCEDURE `increase_salaries_for_departme...
Prześlij komentarz
Czytaj więcej