Java data manipulation sql

Manipulating a Database with JDBC

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Java programs communicate with the database and manipulate its data with the help of the JDBC API. The JDBC driver enables the Java application to connect to a database. JDBC is almost always used with relational databases, also it can be used with any other table based data source. We do not have to worry about the availability of a driver, as major RDBMS (Relational Database Management System) providers provide them free. Apart from that there are many third-party JDBC drivers available.

Basic Requirements

Since we shall be going hands on down the line, the basic software requirements for JDBC programming are as follows.

2.RDBMS Package (For example, MySQL, Oracle, PostgreSQL, etc.)

4.JDBC driver (JDBC drivers are database specific, especially, if we use a driver other than Type1:JDBC-ODBC Bridge. For example, MySQL Connector/J is the official JDBC driver for MySQL, ojdbc for Oracle and so on…PostgreSQL JDBC Driver)

Installation is pretty straightforward; if in doubt, refer to the appropriate installation instruction of the relevant packages during installation.

JDBC Programming Steps

Every Java code in JDBC Programming goes through the following six steps in one way or the other. These steps give an idea about what order to follow during coding and a basic insight into their significance.

1. Importing java.sql Package

Almost all the classes and interfaces used in JDBC programming are compiled in the java.sql package. As a result it is our primary requirement to import the package as follows.

import java.sql.*;

2. Load and Register JDBC Driver

The most common and easiest way to load the driver is by using the Class.forName() method.

Class.forName("com.mysql.jdbc.Driver");

This method takes the complete package name of the driver as its argument. Once the driver is loaded, it will call the DriverManager.registerDriver() method to register itself. Registering a driver implies that the currently registered driver is added to a list of available Driver objects maintained by the DriverManager. The driver manager serves the request from the application using one of the lists of available Driver objects.

3. Establishing Connection

The standard method to establish a connection to a database is through the method call DriverManager.getConnection(). The arguments accepted by this method are: a string representation of the database URL, the user name to log in to the database and the password.

DriverManager.getConnection("jdbc:mysql://localhost/hr","user1","pass");

4. Creating a Statement

We need to create a Statement object to execute a static SQL query. Static SQL statements can be updates, inserts, queries and even DDL SQL statements. Statement objects are created with the help of the Connection object’s createStatement() method as follows:

Statement statement = connection.createStatement();

5. Execute SQL Statement and Retrieve Result

We can use the executeQuery() method of the Statement object to fire the query to the database. This method takes an SQL query string as an argument and returns the result as a ResultSet object. The ResultSet object contains both the data returned by the query and methods for retrieving the data.

ResultSet resultSet=statement.executeQuery("SELECT * FROM employees");

The get methods of the ResultSet object can be used to retrieve each of the fields in the record fetched from the database into java variables.

while(resultSet.next())

6. Close Connection

It is highly recommended that an application should close the Connection object and Statement objects explicitly, because, earlier opened connections can cause trouble for the database and open connections are also prone to security threats. Simply add following statements:

statement.close(); connection.close();

Putting it Together

Let us create an application to demonstrate the CRUD operation to manipulate the database records. CRUD stands for Create, Read, Update and Delete. We shall fetch the database record with a Read operation, create a new record and save it into the database with a Create operation, modify the existing record with an Update operation and remove a record from the database with a Delete operation. Observe that the code below is self-explanatory and almost repetitive with a few significant lines actually invoking the change in the operation. To get a deeper grasp on the Java SQL library functions, refer to the Java API documentation.

//import statements. public class Main < private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; private static final String DATABASE_URL = "jdbc:mysql://localhost/hr"; private static final String USERNAME = "admin"; private static final String PASSWORD = "secret"; /* This operation creates the table in the database which otherwise have to be created through SQL DDL. This operation is given for convenience and does not belong to the CRUD operation we are talking of. Nonetheless novice programmer may find it useful as of how to create a table through Java code. */ public static void createTable()< Connection connection = null; Statement statement = null; try < Class.forName(JDBC_DRIVER); connection = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD); statement = connection.createStatement(); //boolean b=statement.execute("DROP TABLE IF EXISTS emp"); boolean b=statement.execute("CREATE TABLE emp(id int primary key,name varchar(15),department int,salary int,location varchar(20))"); if(b==true) System.out.println("Tables created. "); >catch (SQLException sqlEx) < sqlEx.printStackTrace(); System.exit(1); >catch (ClassNotFoundException clsNotFoundEx) < clsNotFoundEx.printStackTrace(); System.exit(1); >finally < try < statement.close(); connection.close(); >catch (Exception e) < System.exit(1); >> > public static void createEmployee(int id, String name, int dept, int sal, String loc) < Connection connection = null; PreparedStatement preparedStatement = null; try < Class.forName(JDBC_DRIVER); connection = DriverManager.getConnection(DATABASE_URL, USERNAME,PASSWORD); preparedStatement = connection.prepareStatement("INSERT INTO emp VALUES(. )"); preparedStatement.setInt(1, id); preparedStatement.setString(2, name); preparedStatement.setInt(3, dept); preparedStatement.setInt(4, sal); preparedStatement.setString(5, loc); boolean b=preparedStatement.execute(); if(b==true) System.out.println("1 record inserted. "); >catch (SQLException sqlEx) < sqlEx.printStackTrace(); System.exit(1); >catch (ClassNotFoundException clsNotFoundEx) < clsNotFoundEx.printStackTrace(); System.exit(1); >finally < try < preparedStatement.close(); connection.close(); >catch (Exception e) < System.exit(1); >> > public static void updateSalary(int id, int raise) < Connection connection = null; PreparedStatement preparedStatement = null; try < Class.forName(JDBC_DRIVER); connection = DriverManager.getConnection(DATABASE_URL, USERNAME,PASSWORD); preparedStatement = connection.prepareStatement("UPDATE emp SET salary=salary+? WHERE preparedStatement.setInt(1, raise); preparedStatement.setInt(2, id); boolean b=preparedStatement.execute(); if(b==true) System.out.println("$"+raise+" raised for emp DELETE FROM emp WHERE preparedStatement.setInt(1, id); boolean b=preparedStatement.execute(); if(b==true) System.out.println("1 record deleted. "); >catch (SQLException sqlEx) < sqlEx.printStackTrace(); System.exit(1); >catch (ClassNotFoundException clsNotFoundEx) < clsNotFoundEx.printStackTrace(); System.exit(1); >finally < try < preparedStatement.close(); connection.close(); >catch (Exception e) < System.exit(1); >> > public static void readEmployees() < Connection connection = null; Statement statement = null; try < Class.forName(JDBC_DRIVER); connection = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD); statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT * FROM emp"); ResultSetMetaData metaData = resultSet.getMetaData(); int noCols = metaData.getColumnCount(); for (int i = 1; i System.out.println(); while (resultSet.next()) < for (int i = 1; i System.out.println(); > > catch (SQLException sqlEx) < sqlEx.printStackTrace(); System.exit(1); >catch (ClassNotFoundException clsNotFoundEx) < clsNotFoundEx.printStackTrace(); System.exit(1); >finally < try < statement.close(); connection.close(); >catch (Exception e) < System.exit(1); >> > public static void readEmployee(int id) < Connection connection = null; Statement statement = null; try < Class.forName(JDBC_DRIVER); connection = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD); statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT * FROM emp WHERE %-10st", metaData.getColumnName(i).toUpperCase()); >System.out.println(); while (resultSet.next()) < for (int i = 1; i System.out.println(); > > catch (SQLException sqlEx) < sqlEx.printStackTrace(); System.exit(1); >catch (ClassNotFoundException clsNotFoundEx) < clsNotFoundEx.printStackTrace(); System.exit(1); >finally < try < statement.close(); connection.close(); >catch (Exception e) < System.exit(1); >> > public static void main(String[] args) < //createTable(); createEmployee(1234, "Larson", 123, 1200, "New Jersey"); createEmployee(5678, "Jones", 123, 1100, "New Jersey"); createEmployee(7890, "Kapil", 345, 1600, "Los Angeles"); createEmployee(2341, "Myers", 123, 1800, "New Jersey"); createEmployee(6784, "Bruce", 345, 2200, "Los Angeles"); createEmployee(9636, "Neumann", 123, 3200, "New Jersey"); updateSalary(1234, 1000); createEmployee(1111, "Lee", 123, 4400, "New Jersey"); deleteEmployee(1111); readEmployees(); readEmployee(6784); >>

Conclusion

Once one gets an idea of the CRUD operations of JDBC programming and how to write them, it’s just a matter of practice to master the intricacies of database manipulation. The above example is very minimalistic and a lot of checking/cross checking during data manipulation has been overlooked to keep it simple. For example the code can have a search method, which would actually use the same CRUD techniques, a checking should be there to verify the existence of a record before updating a record etc. However, if one grasps the CRUD techniques described above in Java code, the rest is just a walk in the park.

Источник

Часть 1. Введение в SQL

Эта статья открывает небольшой цикл, посвященный азам взаимодействия с базами данных (БД) в Java и введению в SQL. Многие программы заняты обработкой и модификацией информации, её поддержкой в актуальном состоянии. Поскольку данные — весьма важная часть логики программ, то под них зачастую выделяют отдельное хранилище. Информация в нём структурирована и подчинена специальным правилам, чтобы обеспечить правильность обработки и хранения. Доступ к данным и их изменение осуществляется с помощью специального языка запросов — SQL (Structured Query Language).

Система управления базами данных — это ПО, которое обеспечивает взаимодействие разных внешних программ с данными и дополнительные службы (журналирование, восстановление, резервное копирование и тому подобное), в том числе посредством SQL. То есть программная прослойка между данными и внешними программами с ними работающими. В этой части ответим на вопросы что такое SQL, что такое SQL сервер и создадим первую программу для взаимодействия с СУБД.

Виды СУБД

• Иерархические. Данные организованы в виде древовидной структуры. Пример — файловая система, которая начинается с корня диска и далее прирастает ветвями файлов разных типов и папок разной степени вложенности.
• Сетевые. Видоизменение иерархической, у каждого узла может быть больше одного родителя.
• Объектно-ориентированные. Данные организованы в виде классов/объектов c их атрибутами и принципами взаимодействия согласно ООП.
• Реляционные. Данные этого вида СУБД организованы в таблицах. Таблицы могут быть связаны друг с другом, информация в них структурирована.

SQL

• Что такое SQL-Сервер и как он работает? Взаимодействие с СУБД происходит по клиент-серверному принципу. Некая внешняя программа посылает запрос в виде операторов и команд на языке SQL, СУБД его обрабатывает и высылает ответ. Для упрощения примем, что SQL Сервер = СУБД.

• Data Definition Language (DDL) – определения данных. Создание структуры БД и её объектов;
• Data Manipulation Language(DML) – собственно взаимодействие с данными: вставка, удаление, изменение и чтение;
• Transaction Control Language (TCL) – управление транзакциями;
• Data Control Language(DCL) – управление правами доступа к данным и структурам БД.

JDBC

В 80-е годы прошлого века персональные компьютеры типа PC XT/AT завоевали рынок. Во многом это произошло благодаря модульности их конструкции. Это означает, что пользователь мог довольно просто менять ту или иную составную часть своего компьютера (процессор, видеокарту, диски и тому подобное). Это замечательное свойство сохранилось и поныне: мы меняем видеокарту и обновляем драйвер (иногда он и вовсе обновляется сам, в автоматическом режиме). Чаще всего при таких манипуляциях ничего плохого не происходит, и существующие программы продолжат работать с обновившейся системой без переустановки. Аналогично и для работы в Java с СУБД. Для стандартизации работы с SQL-серверами взаимодействие с ней можно выполнять через единую точку — JDBC (Java DataBase Connectivity). Она представляет собой реализацию пакета java.sql для работы с СУБД. Производители всех популярных SQL-серверов выпускают для них драйверы JDBC. Рассмотрим схему ниже. Приложение использует экземпляры классов из java.sql. Затем мы передаем необходимые команды для получения/модификации данных. Далее java.sql через jdbc-драйвер взаимодействует с СУБД и возвращает нам готовый результат. Для перехода на СУБД другого производителя часто достаточно сменить JDBC и выполнить базовые настройки. Остальные части программы при этом не меняются.

Первая программа

Приступим к практической части. Создадим Java-проект с помощью IDE JetBrains IntelliJ IDEA. Заметим, что редакция Ultimate Edition содержит в своём составе замечательный инструмент для работы с SQL и БД — Data Grip. Однако она платная для большинства пользователей. Так что нам для учебных целей остается использовать общедоступную IntelliJ IDEA Community Edition. Итак:

1. Запускаем IDE и создадём новый проект:
   
2. Выбираем Java-проект, указываем версию SDK (в примере JDK8, однако это не критично):
   
3. На следующем шаге выбираем в качестве типа консольное приложение:
   
4. Указываем имя проекта, пакет и его размещение на диске (я создал специально для этого отдельную директорию):
   
5. Отложим на минуту IDE и загрузим c www.h2database.com необходимый JDBC-файл для работы c СУБД H2 (download platform independent ZIP):
   
6. Заходим внутрь скачанного файла (нас интересует jar-файл по пути h2\bin, который нам далее понадобится, скопируем его):
   
7. Возвращаемся в IDE и создаём в корне проекта директории: db, где будут размещены файлы с данными СУБД; lib – здесь JAR-библиотека JDBC:
   
8. Переносим в директорию lib jar-файл из шага 6, и добавим его в проект как библиотеку:
   
9. Переименуем java-файл в src/sql/demo на StockExchange.java (если забыли, мы собираемся эмулировать простую «биржу»), поменяем его содержимое и запустим:
   

Теперь мы умеем подключаться к СУБД и отключаться от неё. Каждый шаг отражается в консоли. При первом подключении к СУБД создаётся файл базы данных stockExchange.mv.db.

Разбор кода

 package sql.demo; import java.sql.*; public class StockExchangeDB < // Блок объявления констант public static final String DB_URL = "jdbc:h2:/c:/JavaPrj/SQLDemo/db/stockExchange"; public static final String DB_Driver = "org.h2.Driver"; public static void main(String[] args) < try < Class.forName(DB_Driver); //Проверяем наличие JDBC драйвера для работы с БД Connection connection = DriverManager.getConnection(DB_URL);//соединениесБД System.out.println("Соединение с СУБД выполнено."); connection.close(); // отключение от БД System.out.println("Отключение от СУБД выполнено."); >catch (ClassNotFoundException e) < e.printStackTrace(); // обработка ошибки Class.forName System.out.println("JDBC драйвер для СУБД не найден!"); >catch (SQLException e) < e.printStackTrace(); // обработка ошибок DriverManager.getConnection System.out.println("Ошибка SQL !"); >> > 

Блок констант:

1. DB_Driver: Здесь мы определили имя драйвера, которое можно узнать, например, кликнув мышкой на подключенную библиотеку и развернув её структуру в директории lib текущего проекта.
2. DB_URL: Адрес нашей базы данных. Состоит из данных, разделённых двоеточием:
3. Протокол=jdbc
4. Вендор (производитель/наименование) СУБД=h2
5. Расположение СУБД, в нашем случае путь до файла (c:/JavaPrj/SQLDemo/db/stockExchange). Для сетевых СУБД тут дополнительно указываются имена или IP адреса удалённых серверов, TCP/UDP номера портов и так далее.

Обработка ошибок:

Вызов методов нашего кода может вернуть ошибки, на которые следует обратить внимание. На данном этапе мы просто информируем о них в консоли. Заметим, что ошибки при работе с СУБД — это чаще всего SQLException.

Логика работы:

1. Class.forName(DB_Driver) – убеждаемся в наличии соответствующего JDBC-драйвера (который мы ранее загрузили и установили).
2. DriverManager.getConnection(DB_URL) – устанавливаем соединение СУБД. По переданному адресу, JDBC сама определит тип и местоположение нашей СУБД и вернёт Connection, который мы можем использовать для связи с БД.
3. connection.close() – закрываем соединение с СУБД и завершаем работу с программой.

Источник