This JDBC tutorial is going to help you learning how to do basic database operations (CRUD - Create, Retrieve, Update and Delete) using JDBC (Java Database Connectivity) API. These CRUD operations are equivalent to the INSERT, SELECT, UPDATE and DELETE statements in SQL language. Although the target database system is MySQL, but the same technique can be applied for other database systems as well because the query syntax used is standard SQL which is supported by all relational database systems.We will learn how to do insert, query, update and delete database records by writing code to manage records of a table Users
The relational database model is the most used database model today. However, many other database models exist that provide different strengths than the relational model. The hierarchical database model, popular in the 1960s and 1970s, connected data together in a hierarchy, allowing for a parent/child relationship between data.
AFMAN33-363 1 MARCH 2008 5 Chapter 1 OBJECTIVES AND RESPONSIBILITIES 1.1. Record Management Basics. Records are created by military, civilian, and contractor Air Force employees. Record types include Draft records-those that can be altered and have not been signed.
in a MySQL database called
And Swatman, P.M.C. (2008) “ Innovative Record s Management Solu tions: A Best Practice Exemplar fro m Local Governm ent”, Proc. ICEG 2008 – 4th Internat ional Conferenc e.
SampleDB.Table of content:
1. Prerequisites
To begin, make sure you have the following pieces of software installed on your computer:
JDK (download JDK 7).
MySQL (download MySQL Community Server 5.6.12). You may also want to download MySQL Workbench - a graphical tool for working with MySQL databases.
JDBC Driver for MySQL (download MySQL Connector/J 5.1.25). Extract the zip archive and put the mysql-connector-java-VERSION-bin.jar file into classpath (in a same folder as your Java source files).
2. Creating a sample MySQL database
Let’s create a MySQL database called SampleDB with one table Users with the following structure:Execute the following SQL script inside MySQL Workbench:Or if you are using MySQL Command Line Client program, save the above script into a file, let’s say, SQLScript.sqland execute the following command:
source PathToTheScriptFileSQLScript.sql
Here’s an example screenshot taken while executing the above script in MySQL Command Line Client program:
3. Understand the main JDBC interfaces and classes
Let’s take an overview look at the JDBC’s main interfaces and classes with which we usually work. They are all available under the java.sql package:
DriverManager: this class is used to register driver for a specific database type (e.g. MySQL in this tutorial) and to establish a database connection with the server via its getConnection() method.
Connection: this interface represents an established database connection (session) from which we can create statements to execute queries and retrieve results, get metadata about the database, close connection, etc.
Statement and PreparedStatement: these interfaces are used to execute static SQL query and parameterized SQL query, respectively. Statement is the super interface of the PreparedStatement interface. Their commonly used methods are:
boolean execute(String sql): executes a general SQL statement. It returns true if the query returns a ResultSet, false if the query returns an update count or returns nothing. This method can be used with a Statement only.
int executeUpdate(String sql): executes an INSERT, UPDATE or DELETE statement and returns an update account indicating number of rows affected (e.g. 1 row inserted, or 2 rows updated, or 0 rows affected).
ResultSet executeQuery(String sql): executes a SELECT statement and returns a ResultSet object which contains results returned by the query.
A prepared statement is one that contains placeholders (in form question marks ?) for dynamic values will be set at runtime. For example:
SELECT * from Users WHERE user_id=?
Here the value of user_id is parameterized by a question mark and will be set by one of the setXXX() methods from the PreparedStatement interface, e.g. setInt(int index, int value).
ResultSet: contains table data returned by a SELECT query. Use this object to iterate over rows in the result set using next() method, and get value of a column in the current row using getXXX() methods (e.g. getString(), getInt(), getFloat() and so on). The column value can be retrieved either by index number (1-based) or by column name.
SQLException: this checked exception is declared to be thrown by all the above methods, so we have to catch this exception explicitly when calling the above classes’ methods.
4. Connecting to the database
Supposing the MySQL database server is listening on the default port 3306 at localhost. The following code snippet connects to the database name SampleDB by the user root and password secret:Once the connection was established, we have a Connection object which can be used to create statements in order to execute SQL queries. In the above code, we have to close the connection explicitly after finish working with the database:However, since Java 7, we can take advantage of the try-with-resources statement which will close the connection automatically, as shown in the following code snippet:If you are using Java 7 or later, this approach is recommended. The sample programs in this tutorial are all using this try-with-resources statement to make a database connection.NOTE: For details about connecting to a MySQL database, see the article: Connect to MySQL database via JDBC.
5. JDBC Execute INSERT Statement Example
Let’s write code to insert a new record into the table Users
Records 1 5 6 – Innovative Personal Database Pdf File
with following details:
username: bill
password: secretpass
fullname: Bill Gates
email: bill.gates@microsoft.com
Here’s the code snippet:In this code, we create a parameterized SQL INSERT statement and create a PreparedStatement from the Connection object. To set values for the parameters in the INSERT statement, we use the PreparedStatement‘s setString() methods because all these columns in the table Users are of type VARCHAR which is translated to String type in Java. Note that the parameter index is 1-based (unlike 0-based index in Java array).The PreparedStatement interface provides various setXXX() methods corresponding to each data type, for example:
setBoolean(int parameterIndex, boolean x)
setDate(int parameterIndex, Date x)
setFloat(int parameterIndex, float x)
…
And so on. Which method to be used is depending on the type of the corresponding column in the database table.Finally we call the PreparedStatement’s executeUpdate() method to execute the INSERT statement. This method returns an update count indicating how many rows in the table were affected by the query, so checking this return value is necessary to ensure the query was executed successfully. In this case, executeUpdate() method should return 1 to indicate one record was inserted.
6. JDBC Execute SELECT Statement Example
The following code snippet queries all records from the Users table and print out details for each record:Output:
User #1: bill - secretpass - Bill Gates - bill.gates@microsoft.com
Because the SQL SELECT query here is static so we just create a Statement object from the connection. The while loop iterates over the rows contained in the result set by repeatedly checking return value of the ResultSet’s next() method. The next() method moves a cursor forward in the result set to check if there is any remaining record. For each iteration, the result set contains data for the current row, and we use the ResultSet’s getXXX(column index/column name) method to retrieve value of a specific column in the current row, for example this statement:Retrieves value of the second column in the current row, which is the username field. The value is casted to a String because we know that the username field is of type VARCHAR based on the database schema mentioned previously. Keep in mind that the column index here is 1-based, the first column will be at index 1, the second at index 2, and so on. If you are not sure or don’t know exactly the index of column, so passing a column name would be useful:For other data types, the ResultSet provide appropriate getter methods:
getString()
getInt()
getFloat()
getDate()
getTimestamp()
…
TIPS: Accessing column’s value by column index would provide faster performance then column name.
7. JDBC Executing UPDATE Statement Example
The following code snippet will update the record of “Bill Gates” as we inserted previously:This code looks very similar to the INSERT code above, except the query type is UPDATE.
8. JDBC Execute DELETE Statement Example
The following code snippet will delete a record whose username field contains “bill”:So far we have one through some examples demonstrating how to use JDBC API to execute SQL INSERT, SELECT, UPDATE and DELETE statements. The key points to remember are:
Using a Statement for a static SQL query.
Using a PreparedStatement for a parameterized SQL query and using setXXX() methods to set values for the parameters.
Using execute() method to execute general query.
Using executeUpdate() method to execute INSERT, UPDATE or DELETE query
Using executeQuery() method to execute SELECT query.
Using a ResultSet to iterate over rows returned from a SELECT query, using its next() method to advance to next row in the result set, and using getXXX() methods to retrieve values of columns.
You can download source code of sample demo programs for each type of query in the attachments section. For more interactive hands-on with JDBC CRUD operations, watch this video:NOTE: If you use Spring framework to acces relation database, consider to use Spring JdbcTemplate that simplifies and reduces the code you need to write.
JDBC API References:
Related JDBC Tutorials:
About the Author:
Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.
Attachments:
[Demo program for DELETE statement]
0.9 kB
[Demo program for INSERT statement]
1 kB
[Demo program for SELECT statement]
1 kB
[Demo program for UPDATE statement]
1 kB
[MySQL script to create database and table]
0.2 kB
The State of Vermont Personnel Policies and Procedures Manual is designed to set forth the policies and procedures currently in effect in State Government.
This manual is designed to further the following goals:
To provide a uniform system of human resource administration throughout State Government.
To assist managers in the development of sound management practices and procedures, and to make effective consistent use of human resources throughout State Government.
To promote effective communication among managers, supervisors and employees.
To ensure, protect, and clarify the rights and responsibilities of both the employer and employees.
These policies and procedures are intended to serve as guidelines to assist in the uniform and consistent administration of personnel policies. https://makealivingplayingcrapsmagic-free-bet.peatix.com. This policy manual is designed to provide essential information on how to accomplish an agency/department mission within the administrative framework of Vermont State Government.
The Personnel Policies and Procedures Manual is available in two formats. The entire Manual is in Portable Document Format (pdf). This indexed (bookmarked) pdf document can be viewed, printed and searched for desired text or topics. This can be found in Forms & Documents. Below is an online version with each policy as a single pdf document.
Section 1 – POLICY MANUAL ADMINISTRATION
Number 1.0 - POLICY MANUAL ADMINISTRATION (pdf)
Section 2 - RULES AND REGULATIONS FOR PERSONNEL ADMINISTRATION
Number 2.3 – RULES AND REGULATIONS FOR PERSONNEL ADMINISTRATION (pdf)
Number 3.0 - EQUAL EMPLOYMENT OPPORTUNITY/AFFIRMATIVE ACTION (pdf) Number 3.1 - SEXUAL HARASSMENT (pdf) Number 3.2 - REASONABLE ACCOMMODATION (pdf) Number 3.3 - DISCRIMINATION COMPLAINTS (pdf)
Section 4 - RECRUITMENT AND POSTING OF VACANCIES
Number 4.0 - RECRUITMENT AND POSTING OF VACANCIES (pdf) Number 4.3 - VETERANS’ PREFERENCE (pdf) Number 4.4 - VERIFICATION OF ELIGIBILITY FOR EMPLOYMENT (pdf) Number 4.11 - INTERVIEWING AND REFERENCE CHECKING (pdf)
Section 5 - EMPLOYMENT CATEGORIES
Number 5.1 - EMPLOYMENT CATEGORIES (pdf) Number 5.2 - CONFLICTS OF INTEREST ARISING FROM EMPLOYMENT (pdf) Number 5.3 - PROBATIONARY PERIODS (pdf) Number 5.4 - PERSONNEL RECORDS (pdf) Number 5.6 - EMPLOYEE CONDUCT (pdf) Number 5.7 - POLITICAL ACTIVITY (pdf)
Section 6 - CLASSIFICATION SYSTEM
Number 6.0 - CLASSIFICATION SYSTEM (pdf) Number 6.1 - POSITION EVALUATION SYSTEM (pdf) Number 6.2 - CLASSIFICATION REVIEW (pdf) Number 6.3 - BARGAINING UNIT DESIGNATION (pdf) Number 6.4 - DECENTRALIZED REALLOCATION (pdf) Number 6.7 – INTERNSHIPS (pdf) Number 6.8 - APPROPRIATE USE OF COMMUNICATIONS AND MARKETING POSITIONS IN STATE GOVERNMENT (pdf)
Section 7 - PERFORMANCE MANAGEMENT SYSTEM
Number 7.0 - PERFORMANCE MANAGEMENT (pdf) Number 7.1 - EMPLOYEE RECOGNITION AND MERIT BONUS AWARDS (pdf) Number 7.2 - MERIT AWARD PROCESS FOR CERTAIN EXEMPT EMPLOYEES (pdf) Lounge lizard ep 4 serial number.
Section 8 - DISCIPLINARY ACTION
Number 8.0 - DISCIPLINARY ACTION AND CORRECTIVE ACTION (pdf) Number 8.1 - DUE PROCESS REQUIREMENTS (LOUDERMILL PROCESS) (pdf)
Section 9 - TYPES OF SEPARATION
Number 9.0 - TYPES OF SEPARATION (pdf) Number 9.1 - IMMEDIATE DISMISSAL (pdf) Number 9.2 - FINAL PAY (pdf)
Section 10 - GRIEVANCE PROCEDURE
Number 10.0 - GRIEVANCE PROCEDURE (pdf) Number 10.1 - CLASSIFICATION GRIEVANCE (pdf) Number 10.2 - ADA/ADAA GRIEVANCE PROCEDURE (pdf)
Section 11 - EMPLOYEE WORKWEEK – LOCATION/SHIFT
Number 11.0 - EMPLOYEE WORKWEEK/LOCATION/SHIFT (pdf) Number 11.1 - LUNCH AND BREAK PERIODS (pdf) Number 11.2 - OVERTIME/COMPENSATORY TIME (pdf) Number 11.3 - EMERGENCY CLOSING (pdf) Number 11.4 - WORK RULES (pdf) Number 11.5 - INCOME FROM OUTSIDE SOURCES (MOONLIGHTING) (pdf) Number 11.6 - NO SOLICITATION POLICY (pdf) Number 11.7 - ELECTRONIC COMMUNICATIONS AND INTERNET USE (pdf) Number 11.9 – TELEWORK (pdf) Number 11.10 - TIME ENTRY AND APPROVAL (pdf) Number 11.11 - WORKPLACE SAFETY AND SECURITY (pdf)
Section 12 - COMPENSATION FOR CLASSIFIED EMPLOYEES
Number 12.0 - COMPENSATION FOR CLASSIFIED EMPLOYEES (pdf) Number 12.1 - STEP MOVEMENT (pdf) Number 12.2 - HIRE-INTO-RANGE (pdf) Number 12.3 - MARKET FACTOR ADJUSTMENT (pdf) Number 12.4 - SHIFT DIFFERENTIAL (pdf) Number 12.5 - HIGHER ASSIGNMENT PAY (pdf) Number 12.6 - ON-CALL/CALL-IN/STAND-BY/AVAILABLE STATUS (pdf) Number 12.7 - COMPENSATION FOR TEMPORARY EMPLOYEES (pdf) Number 12.8 - COMPENSATION FOR EXEMPT EMPLOYEES (pdf) Number 12.9 - NOTICE OR PAY IN LIEU OF NOTICE FOR ORIGINAL PROBATIONARY EMPLOYEES (pdf) Number: 12.10 - EMPLOYEE REQUEST FOR SALARY ADVANCE (pdf) Number 12.11 - DIRECT DEPOSIT (pdf) Number 12.12 - TAX COMPLIANCE (pdf)
Section 13 - GROUP MEDICAL BENEFIT PLANS
Number 13.0 - GROUP MEDICAL BENEFIT PLANS (pdf) Number 13.1 - LIFE INSURANCE/ACCIDENTAL DEATH AND DISMEMBERMENT PLAN (pdf) Number 13.2 - DENTAL ASSISTANCE PLAN (pdf) Number 13.3 - FLEXIBLE SPENDING ACCOUNT PLAN (pdf) Number 13.4 - CHILD CARE (pdf) Number 13.5 - RETIREMENT PLAN (pdf) Number 13.6 - DEFERRED COMPENSATION (pdf) Number 13.7 - SOCIAL SECURITY (FICA)(pdf) Number 13.8 - UNEMPLOYMENT COMPENSATION (pdf) Number 13.10 - MEDICAL, LIFE AND DENTAL BENEFITS UPON SEPARATION FROM ACTIVE EMPLOYMENT (pdf) Number 13.11 - CHOICE PLUS WELLNESS SCREENINGS (pdf) Number 13.12 - WHEN AN EMPLOYEE DIES IN ACTIVE SERVICE (pdf) Number 13.13 - INFANTS IN THE WORKPLACE (pdf)
Section 14 - ANNUAL LEAVE
Number 14.0 - ANNUAL LEAVE (pdf) Number 14.1 - SICK LEAVE (pdf) Number 14.2 - FAMILY AND PARENTAL LEAVE (pdf) Number 14.3 - PERSONAL LEAVE (pdf) Number 14.4 - FIRE AND RESCUE DUTY (pdf) Number 14.5 - CIVIC DUTY (pdf) Number 14.6 - COURT AND JURY DUTY/ WITNESS FEES (pdf) Number 14.7 - MILITARY LEAVE (pdf) (UNDER REVIEW) Number 14.8 – HOLIDAYS (pdf) Number 14.9 - MISCELLANEOUS LEAVE (pdf)
Section 15: TRAINING AND CAREER DEVELOPMENT OPPORTUNITIES
Records 1 5 6 – Innovative Personal Database Pdf Software
Number 15.0 - TRAINING AND CAREER DEVELOPMENT OPPORTUNITIES (pdf) Number 15.1 - EDUCATIONAL LEAVE (pdf) Number 15.2 - TUITION REIMBURSEMENT (pdf)
Section 16 - VERMONT STATE EMPLOYEES' ASSOCIATION, INC.
Records 1 5 6 – Innovative Personal Database Pdf Template
Section 17 - EMPLOYMENT RELATED INVESTIGATIONS
Number 17.0 - EMPLOYMENT RELATED INVESTIGATIONS (pdf) Number 17.2 - ALCOHOL AND CONTROLLED SUBSTANCE TESTING (pdf) Number 17.3 - DRUG-FREE WORKPLACE POLICY (pdf) Number 17.4 - STATEWIDE SMOKING POLICY (pdf) Number 17.5 - BLOODBORNE PATHOGENS (pdf) Number 17.7 - DOMESTIC AND SEXUAL VIOLENCE POLICY (pdf)
root and password secret:Once the connection was established, we have a Connection object which can be used to create statements in order to execute SQL queries. In the above code, we have to close the connection explicitly after finish working with the database:However, since Java 7, we can take advantage of the try-with-resources statement which will close the connection automatically, as shown in the following code snippet:If you are using Java 7 or later, this approach is recommended. The sample programs in this tutorial are all using this try-with-resources statement to make a database connection.NOTE: For details about connecting to a MySQL database, see the article: Connect to MySQL database via JDBC.
You can download source code of sample demo programs for each type of query in the attachments section. For more interactive hands-on with JDBC CRUD operations, watch this video:NOTE: If you use Spring framework to acces relation database, consider to use Spring JdbcTemplate that simplifies and reduces the code you need to write.
0 
Star