Download all versions of mysql-connector-java jar files with all dependencies

Разбор кода

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

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

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

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

Вторая частьТретья часть

Интерфейс ResultSet

Этот интерфейс представляет результирующий набор базы данных. Он обеспечивает приложению построчный доступ к результатам запросов в базе данных.


Во время обработки запроса ResultSet поддерживает указатель на текущую обрабатываемую строку. Приложение последовательно перемещается по результатам, пока они не будут все обработаны или не будет закрыт ResultSet.

Основные методы интерфейса ResultSet:

  1. public boolean absolute(int row) throws SQLException — метод перемещает курсор на заданное число строк от начала, если число положительно, и от конца — если отрицательно.
  2. public void afterLast() throws SQLException — этот метод перемещает курсор в конец результирующего набора за последнюю строку.
  3. public void beforeFirst() throws SQLException — этот метод перемещает курсор в начало результирующего набора перед первой строкой.
  4. public void deleteRow() throws SQLException — удаляет текущую строку из результирующего набора и базы данных.
  5. public ResultSetMetaData getMetaData() throws SQLException — предоставляет объект метаданных для данного ResultSet. Класс ResultSetMetaData содержит информацию о результирующей таблице, такую как количество столбцов, их заголовок и т.д.
  6. public int getRow() throws SQLException — возвращает номер текущей строки.
  7. public Statement getStatement() throws SQLException — возвращает экземпляр Statement, который произвел данный результирующий набор.
  8. public boolean next() throws SQLException, public boolean previous() throws SQLException — эти методы позволяют переместиться в результирующем наборе на одну строку вперед или назад. Во вновь созданном результирующем наборе курсор устанавливается перед первой строкой, поэтому первое обращение к методу next() влечет позиционирование на первую строку. Эти методы возвращают true, если остается строка для дальнейшего перемещения. Если строк для обработки больше нет, возвращается false.
  9. public void close() throws SQLException — осуществляет немедленное закрытие ResultSet вручную. Обычно этого не требуется, так как закрытие Statement, связанного с ResultSet, автоматически закрывает ResultSet.

Create databases, tables, and populate tables

If you are using MySQL, then run the following command to create a database:

ant create-mysql-database

Note: No corresponding Ant target exists in the file that creates a database for Java DB. The database URL for Java DB, which is used to establish a database connection, includes the option to create the database (if it does not already exist). See Establishing a Connection for more information.

If you are using either Java DB or MySQL, then from the same directory, run the following command to delete existing sample database tables, recreate the tables, and populate them. For Java DB, this command also creates the database if it does not already exist:

ant setup

Note: You should run the command every time before you run one of the Java classes in the sample. Many of these samples expect specific data in the contents of the sample’s database tables.

Deploying and Testing the Application

this tutorial

http://localhost:8080/Bookstore

Add New BookSaveEditDeleteAttachments

Related Tutorials:

  • Java Servlet and JSP for beginners
  • JDBC Create, Retrieve, Update and Delete (CRUD) Tutorial
  • Handling Error for Java web applications

Other Java Servlet Tutorials:

  • Java Servlet Quick Start for beginners (XML)
  • Java Servlet for beginners (annotations)
  • Handling HTML form data with Java Servlet
  • Java File Download Servlet Example
  • Upload file to servlet without using HTML form
  • How to use Cookies in Java web application
  • How to use Session in Java web application
Attachments:
Bookstore.zip 22 kB

Notify me of follow-up comments

Send Cancel

Modify the build.xml file

The file is the build file that Apache Ant uses to compile and execute the JDBC samples. The files and contain additional Apache Ant properties required for Java DB and MySQL, respectively. The files and contain properties required by the sample.

Modify these XML files as follows:

Modify build.xml

In the file, modify the property to refer to either or , depending on your DBMS. For example, if you are using Java DB, your file would contain this:

<property
  name="ANTPROPERTIES"
  value="properties/javadb-build-properties.xml"/>

  <import file="${ANTPROPERTIES}"/>

Similarly, if you are using MySQL, your file would contain this:

<property
  name="ANTPROPERTIES"
  value="properties/mysql-build-properties.xml"/>

  <import file="${ANTPROPERTIES}"/>

Modify database-specific properties file

In the or file (depending on your DBMS), modify the following properties, as described in the following table:

Property Description
The full path name of your Java compiler,
The full path name of your Java runtime executable,
The name of the properties file, either or
The full path name of your MySQL driver. For Connector/J, this is typically .
The full path name of your Java DB driver. This is typically .
The full path name of the directory that contains Apache Xalan.
The class path that the JDBC tutorial uses. You do not need to change this value.
The full path name of the file .
A value of either or depending on whether you are using Java DB or MySQL, respectively. The tutorial uses this value to construct the URL required to connect to the DBMS and identify DBMS-specific code and SQL statements.
The fully qualified class name of the JDBC driver. For Java DB, this is . For MySQL, this is .
The host name of the computer hosting your DBMS.
The port number of the computer hosting your DBMS.
The name of the database the tutorial creates and uses.
The connection URL used to connect to your DBMS when creating a new database. You do not need to change this value.
The connection URL used to connect to your DBMS. You do not need to change this value.
The name of the user that has access to create databases in the DBMS.
The password of the user specified in .
The character used to separate SQL statements. Do not change this value. It should be the semicolon character ().

Third-Party MySQL APIs

The remaining APIs described in this chapter provide an interface to MySQL from specific application languages. These third-party solutions are not developed or supported by Oracle. Basic information on their usage and abilities is provided here for reference purposes only.

All the third-party language APIs are developed using one of two methods, using or by implementing a native driver. The two solutions offer different benefits:

  • Using offers complete compatibility with MySQL because it uses the same libraries as the MySQL client applications. However, the feature set is limited to the implementation and interfaces exposed through and the performance may be lower as data is copied between the native language, and the MySQL API components.

  • Native drivers are an implementation of the MySQL network protocol entirely within the host language or environment. Native drivers are fast, as there is less copying of data between components, and they can offer advanced functionality not available through the standard MySQL API. Native drivers are also easier for end users to build and deploy because no copy of the MySQL client libraries is needed to build the native driver components.

lists many of the libraries and interfaces available for MySQL.

Table 27.1 MySQL APIs and Interfaces

Environment API Type Notes
Ada GNU Ada MySQL Bindings See MySQL Bindings for GNU Ada
C C API See MySQL 5.7 C API Developer Guide.
C++ Connector/C++ See MySQL Connector/C++ 8.0 Developer Guide.
MySQL++ See MySQL++ website.
MySQL wrapped See MySQL wrapped.
Cocoa MySQL-Cocoa Compatible with the Objective-C Cocoa environment. See http://mysql-cocoa.sourceforge.net/
D MySQL for D See MySQL for D.
Eiffel Eiffel MySQL See Section 27.13, “MySQL Eiffel Wrapper”.
Erlang See .
Haskell Haskell MySQL Bindings Native Driver See Brian O’Sullivan’s pure Haskell MySQL bindings.
See MySQL driver for Haskell.
Java/JDBC Connector/J Native Driver See MySQL Connector/J 5.1 Developer Guide.
Kaya MyDB See MyDB.
Lua LuaSQL See LuaSQL.
.NET/Mono Connector/NET Native Driver See MySQL Connector/NET Developer Guide.
Objective Caml OBjective Caml MySQL Bindings See MySQL Bindings for Objective Caml.
Octave Database bindings for GNU Octave See Database bindings for GNU Octave.
ODBC Connector/ODBC See MySQL Connector/ODBC Developer Guide.
Perl See Section 27.9, “MySQL Perl API”.
Native Driver See at CPAN
PHP , interface (deprecated) See Original MySQL API.
, interface See MySQL Improved Extension.
See MySQL Functions (PDO_MYSQL).
PDO mysqlnd Native Driver
Python Connector/Python Native Driver See MySQL Connector/Python Developer Guide.
Python Connector/Python C Extension See MySQL Connector/Python Developer Guide.
MySQLdb See Section 27.10, “MySQL Python API”.
Ruby MySQL/Ruby Uses . See Section 27.11.1, “The MySQL/Ruby API”.
Ruby/MySQL Native Driver See Section 27.11.2, “The Ruby/MySQL API”.
Scheme See .
SPL See for SPL.
Tcl MySQLtcl See Section 27.12, “MySQL Tcl API”.

Release roadmap and standards


Our goal is to release regular updates which improve the driver and bring new features to users. Stable, production quality releases happen twice a year, targeting the first and third quarters of the calendar year. They are tested against a comprehensive matrix of supported operating systems, Java versions, and SQL Server versions. Stable releases are accompanied by additional localized packages, which are available on the Microsoft website.

Preview releases happen approximately monthly between stable releases. This gives users an opportunity to try out new features and provide feedback on them before they go into stable releases. Preview releases also include frequent bug fixes for customers to verify without having to wait for a stable release. Preview releases are only available in English. While they are tested, preview releases do not necessarily go through the same rigorous, full test matrix and review process as stable releases.

You can see what is going into a future release by monitoring Milestones in the repository.

Version conventions

Starting with 6.0, stable versions have an even minor version. For example, 6.0, 6.2, 6.4, 7.0, 7.2, 7.4, 8.2, 8.4. Preview versions have an odd minor version. For example, 6.1, 6.3, 6.5, 7.1, 7.3, 8.1 and so on

Виды СУБД

Существует несколько видов СУБД по способу организации хранения данных:

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

Вопрос:Ответ:

Coding DAO class

bookBookDAO

package net.codejava.javaee.bookstore;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * AbstractDAO.java
 * This DAO class provides CRUD database operations for the table book
 * in the database.
 * @author www.codejava.net
 *
 */
public class BookDAO {
	private String jdbcURL;
	private String jdbcUsername;
	private String jdbcPassword;
	private Connection jdbcConnection;
	
	public BookDAO(String jdbcURL, String jdbcUsername, String jdbcPassword) {
		this.jdbcURL = jdbcURL;
		this.jdbcUsername = jdbcUsername;
		this.jdbcPassword = jdbcPassword;
	}
	
	protected void connect() throws SQLException {
		if (jdbcConnection == null || jdbcConnection.isClosed()) {
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException e) {
				throw new SQLException(e);
			}
			jdbcConnection = DriverManager.getConnection(
										jdbcURL, jdbcUsername, jdbcPassword);
		}
	}
	
	protected void disconnect() throws SQLException {
		if (jdbcConnection != null && !jdbcConnection.isClosed()) {
			jdbcConnection.close();
		}
	}
	
	public boolean insertBook(Book book) throws SQLException {
		String sql = "INSERT INTO book (title, author, price) VALUES (?, ?, ?)";
		connect();
		
		PreparedStatement statement = jdbcConnection.prepareStatement(sql);
		statement.setString(1, book.getTitle());
		statement.setString(2, book.getAuthor());
		statement.setFloat(3, book.getPrice());
		
		boolean rowInserted = statement.executeUpdate() > 0;
		statement.close();
		disconnect();
		return rowInserted;
	}
	
	public List<Book> listAllBooks() throws SQLException {
		List<Book> listBook = new ArrayList<>();
		
		String sql = "SELECT * FROM book";
		
		connect();
		
		Statement statement = jdbcConnection.createStatement();
		ResultSet resultSet = statement.executeQuery(sql);
		
		while (resultSet.next()) {
			int id = resultSet.getInt("book_id");
			String title = resultSet.getString("title");
			String author = resultSet.getString("author");
			float price = resultSet.getFloat("price");
			
			Book book = new Book(id, title, author, price);
			listBook.add(book);
		}
		
		resultSet.close();
		statement.close();
		
		disconnect();
		
		return listBook;
	}
	
	public boolean deleteBook(Book book) throws SQLException {
		String sql = "DELETE FROM book where book_id = ?";
		
		connect();
		
		PreparedStatement statement = jdbcConnection.prepareStatement(sql);
		statement.setInt(1, book.getId());
		
		boolean rowDeleted = statement.executeUpdate() > 0;
		statement.close();
		disconnect();
		return rowDeleted;		
	}
	
	public boolean updateBook(Book book) throws SQLException {
		String sql = "UPDATE book SET title = ?, author = ?, price = ?";
		sql += " WHERE book_id = ?";
		connect();
		
		PreparedStatement statement = jdbcConnection.prepareStatement(sql);
		statement.setString(1, book.getTitle());
		statement.setString(2, book.getAuthor());
		statement.setFloat(3, book.getPrice());
		statement.setInt(4, book.getId());
		
		boolean rowUpdated = statement.executeUpdate() > 0;
		statement.close();
		disconnect();
		return rowUpdated;		
	}
	
	public Book getBook(int id) throws SQLException {
		Book book = null;
		String sql = "SELECT * FROM book WHERE book_id = ?";
		
		connect();
		
		PreparedStatement statement = jdbcConnection.prepareStatement(sql);
		statement.setInt(1, id);
		
		ResultSet resultSet = statement.executeQuery();
		
		if (resultSet.next()) {
			String title = resultSet.getString("title");
			String author = resultSet.getString("author");
			float price = resultSet.getFloat("price");
			
			book = new Book(id, title, author, price);
		}
		
		resultSet.close();
		statement.close();
		
		return book;
	}
}
  • Create: insertBook(Book)- this inserts a new row into the table book.
  • Read: listAllBooks() — this retrieves all rows; and getBook(id)- returns a specific row based on the primary key value (ID).
  • Update: updateBook(Book)- this updates an existing row in the database.
  • Delete: deleteBook(Book) — this removes an existing row in the database based on the primary key value (ID).

SQL Insert, Select, Update, and Delete Examples

Writing Model Class

Book.java

package net.codejava.javaee.bookstore;

/**
 * Book.java
 * This is a model class represents a book entity
 * @author www.codejava.net
 *
 */
public class Book {
	protected int id;
	protected String title;
	protected String author;
	protected float price;

	public Book() {
	}

	public Book(int id) {
		this.id = id;
	}

	public Book(int id, String title, String author, float price) {
		this(title, author, price);
		this.id = id;
	}
	
	public Book(String title, String author, float price) {
		this.title = title;
		this.author = author;
		this.price = price;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getAuthor() {
		return author;
	}

	public void setAuthor(String author) {
		this.author = author;
	}

	public float getPrice() {
		return price;
	}

	public void setPrice(float price) {
		this.price = price;
	}
}

book

The MySQL C API

For direct access to using MySQL natively within a C application, there are two methods:

  • The C API provides low-level access to the MySQL client/server protocol through the client library. This is the primary method used to connect to an instance of the MySQL server, and is used both by MySQL command-line clients and many of the MySQL Connectors and third-party APIs detailed here.

    is included in MySQL distributions.

  • is an embedded MySQL server library that enables you to embed an instance of the MySQL server into your C applications.

    is included in MySQL distributions.

    Note

    The embedded server library is deprecated as of MySQL 5.7.19 and is removed in MySQL 8.0.

See also MySQL C API Implementations.

To access MySQL from a C application, or to build an interface to MySQL for a language not supported by the Connectors or APIs in this chapter, the C API is where to start. A number of programmer’s utilities are available to help with the process; see Section 4.7, “Program Development Utilities”.

The MySQL C API

For direct access to using MySQL natively within a C application, the C API provides low-level access to the MySQL client/server protocol through the client library. This is the primary method used to connect to an instance of the MySQL server, and is used both by MySQL command-line clients and many of the MySQL Connectors and third-party APIs detailed here.

is included in MySQL distributions distributions.

See also MySQL C API Implementations.

To access MySQL from a C application, or to build an interface to MySQL for a language not supported by the Connectors or APIs in this chapter, the C API is where to start. A number of programmer’s utilities are available to help with the process; see Section 4.7, “Program Development Utilities”.

Actions

This operation deletes a row from a table.

This operation gets a row from a table.

This operation gets rows from a table.

This operation gets tables from a database.

This operation inserts a new row into a table.

This operation updates an existing row in a table.

Delete row

Operation ID:
DeleteItem

This operation deletes a row from a table.

Parameters

Name Key Required Type Description
Table name table True string

Name of table

Row id id True string

Primary key of the row to delete

Get row

Operation ID:
GetItem

This operation gets a row from a table.

Parameters

Name Key Required Type Description
Table name table True string

Name of MySql table

Row id id True string

Primary key of the row to retrieve

Get rows

Operation ID:
GetItems

This operation gets rows from a table.

Parameters

Name Key Required Type Description
Table name table True string

Name of MySql table

Filter Query $filter string

An ODATA filter query to restrict the entries returned (e.g. stringColumn eq ‘string’ OR numberColumn lt 123).

Order By $orderby string

An ODATA orderBy query for specifying the order of entries.

Maximum Get Count $top integer

Maximum number of entries to retrieve (default = 512).

Skip Count $skip integer

The number of entries to skip (default = 0).

Select Query $select string

Specific fields to retrieve from entries (default = all).

Operation ID:
GetTables

This operation gets tables from a database.

Represents a list of tables.

Body

Insert row

Operation ID:
PostItem

This operation inserts a new row into a table.

Parameters

Name Key Required Type Description
Table name table True string

Name of table

Row item True dynamic

Row to insert into the specified table

Operation ID:
PatchItem

This operation updates an existing row in a table.

Parameters

Name Key Required Type Description
Table name table True string

Name of table

Row id id True string

Primary key of the row to update

Row item True dynamic

Row with updated values

Выполнение команд. Метод executeUpdate

Последнее обновление: 08.08.2018

Для взаимодействия с базой данных приложение отправляет серверу MySQL команды на языке SQL. Чтобы выполнить команду, вначале необходимо создаеть объект Statement.

Для его создания у объекта Connection вызывается метод createStatement():

Statement statement = conn.createStatement();

Для выполнения команд SQL в классе Statement определено три метода:

  • executeUpdate: выполняет такие команды, как INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE. В качестве результата возвращает количество строк, затронутых операцией (например, количество добавленных, измененных или удаленных строк), или 0, если ни одна строка не затронута операцией или если команда не изменяет содержимое таблицы (например, команда создания новой таблицы)

  • executeQuery: выполняет команду SELECT. Возвращает объект ResultSet, который содержит результаты запроса.

  • execute(): выполняет любые команды и возвращает значение boolean: true — если команда возвращает набор строк (SELECT), иначе возвращается false.

Рассмотрим метод executeUpdate(). В качестве параметра в него передается собственно команда SQL:

int executeUpdate("Команда_SQL")

Ранее была создана база данных store, но она пустая, в ней нет таблиц и соответственно данных. Создадим таблицу и добавим в нее начальные данные:

import java.sql.*;

public class Program{
      
    public static void main(String[] args) {
         try{
			 String url = "jdbc:mysql://localhost/store?serverTimezone=Europe/Moscow&useSSL=false";
			 String username = "root";
			 String password = "password";
			 Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance();
			 // команда создания таблицы
			 String sqlCommand = "CREATE TABLE products (Id INT PRIMARY KEY AUTO_INCREMENT, ProductName VARCHAR(20), Price INT)";
			 
			 try (Connection conn = DriverManager.getConnection(url, username, password)){
				 
				Statement statement = conn.createStatement();
				// создание таблицы
				statement.executeUpdate(sqlCommand);
				
				System.out.println("Database has been created!");
			 }
		 }
		 catch(Exception ex){
			 System.out.println("Connection failed...");
			 
			 System.out.println(ex);
		 }
    }
}

То есть в данном случае мы выполняем команду , которая создает таблицу Products с тремя столбцами: Id — индентификатор стоки, ProductName — строковое название товара и Price — числовая цена товара.

При этом если необходимо выполнить сразу несколько команд, то необязательно создавать новый объект Statement:

Statement statement = conn.createStatement();

statement.executeUpdate("Команда_SQL1");
statement.executeUpdate("Команда_SQL2");
statement.executeUpdate("Команда_SQL3");

НазадВперед


С этим читают