Table of Contents
Preface and Legal Notices
1 General Information
1.1 What Is New in MySQL Workbench
1.1.1 New in MySQL Workbench 8.0 Release Series
1.1.2 New in MySQL Workbench 6.0 Release Series
1.2 MySQL Workbench Editions
2 Installation
2.1 System Requirements
2.2 Command-Line Options
2.3 MySQL Workbench on Windows
2.3.1 Installing
2.3.2 Launching
2.3.3 Uninstalling
2.4 MySQL Workbench on Linux
2.4.1 Installing
2.4.2 Launching
2.4.3 Uninstalling
2.5 MySQL Workbench on macOS
2.5.1 Installing
2.5.2 Launching
2.5.3 Uninstalling
3 Configuration
3.1 User Accessibility Options
3.2 Workbench Preferences
3.2.1 General Editors Preferences
3.2.2 SQL Editor Preferences
3.2.3 Administration Preferences
3.2.4 Modeling Preferences
3.2.5 Fonts and Colors Preferences
3.2.6 SSH Preferences
3.2.7 Other Preferences
3.3 MySQL Workbench Settings and Log Files
3.4 Common Preferences and Configurations
4 Home Screen Tab
5 Connections in MySQL Workbench
5.1 Creating A New MySQL Connection (Simple)
5.2 Creating A New MySQL Connection (Tutorial)
5.3 Manage Server Connections
5.3.1 Standard TCP/IP Connection Method
5.3.2 Local Socket/Pipe Connection Method
5.3.3 Standard TCP/IP over SSH Connection Method
5.3.4 SSL Wizard (Certificates)
5.3.5 Remote Management
5.3.6 System Profile
5.3.7 Configure Server Management Wizard
5.3.8 The Password Storage Vault
5.3.9 Updating Old Authentication Protocol Passwords
5.4 Client Connections
6 Administrative Tasks
6.1 Server Administration
6.1.1 Server Logs
6.1.2 Service Control
6.1.3 Configuration (Options File)
6.2 Users and Privileges
6.3 Server Status
6.4 Status and System Variables
6.5 Data Export and Import
6.5.1 Table Data Export and Import Wizard
6.5.2 SQL Data Export and Import Wizard
6.5.3 Result Data Export and Import
6.6 MySQL Audit Inspector Interface
6.7 MySQL Enterprise Backup Interface
6.7.1 General Requirements
6.7.2 Online Backup
6.7.3 Backup Recovery
6.8 MySQL Enterprise Firewall Interface
6.9 wbcopytables Utility
7 Performance Tools
7.1 Performance Dashboard
7.2 Performance Schema Reports
7.3 Query Statistics
7.4 Visual Explain Plan
7.5 Tutorial: Using Explain to Improve Query Performance
8 Database Development
8.1 Visual SQL Editor
8.1.1 SQL Query Tab
8.1.2 SQL Query Toolbar
8.1.3 Query and Edit Menus
8.1.4 Result Grid
8.1.5 SQL Additions - Snippets Tab
8.1.6 SQL Additions - Context Help Tab
8.1.7 Output Panel
8.1.8 Table Data Search Tab
8.1.9 Export or Import a Table
8.1.10 MySQL Table Editor
8.1.11 Code Generation Overview
8.2 Object Management
8.2.1 Object Browser and Editor Navigator
8.2.2 Session and Object Information Panel
8.2.3 Schema and Table Inspector
9 Database Design and Modeling
9.1 Modeling Interface
9.1.1 Model Editor
9.1.2 EER Diagram Editor
9.1.3 Creating Tables
9.1.4 Creating Foreign Key Relationships
9.1.5 Creating Views
9.1.6 Creating Routines and Routine Groups
9.1.7 Creating Layers
9.1.8 Creating Notes
9.1.9 Creating Text Objects
9.1.10 Creating Images
9.2 Additional Modeling Tools
9.2.1 Printing Diagrams
9.2.2 DBDoc Model Reporting
9.2.3 Schema Validation Plugins
9.3 Modeling Tutorials
9.3.1 Creating a Model
9.3.2 Basic Modeling
9.3.3 Importing a Data Definition SQL Script
9.3.4 Using the Default Schema
9.3.5 Documenting the sakila Database
9.4 Forward and Reverse Engineering
9.4.1 Forward Engineering
9.4.2 Reverse Engineering
9.5 Schema Synchronization and Comparison
9.5.1 Database Synchronization
9.5.2 Compare and Report Differences in Catalogs
9.6 Table Templates
9.7 Customizing DBDoc Model Reporting Templates
9.7.1 Supported Template Markers
9.7.2 Creating a Custom Template
10 Database Migration Wizard
10.1 General Installation Requirements
10.1.1 ODBC Libraries
10.1.2 ODBC Drivers
10.2 Migration Overview
10.2.1 A Visual Guide to Performing a Database Migration
10.2.2 Migrating from Supported Databases
10.2.3 Migrating from Unsupported (Generic) Databases
10.3 Conceptual DBMS Equivalents
10.4 Microsoft Access Migration
10.5 Microsoft SQL Server Migration
10.5.1 Preparations
10.5.2 Drivers
10.5.3 Connection Setup
10.5.4 Microsoft SQL Server Type Mapping
10.6 PostgreSQL migration
10.6.1 Preparations
10.6.2 Drivers
10.6.3 Connection Setup
10.6.4 PostgreSQL Type Mapping
10.7 MySQL Migration
10.8 Using the MySQL Workbench Migration Wizard
10.8.1 Connecting to the Databases
10.8.2 Schema Retrieval and Selection
10.8.3 Reverse Engineering
10.8.4 Object Selection
10.8.5 Migration
10.8.6 Manual Editing
10.8.7 Target Creation Options
10.8.8 Schema Creation
10.8.9 Create Target Results
10.8.10 Data Transfer and Migration Setup
10.8.11 Bulk Data Transfer
10.8.12 Migration Report
10.9 MySQL Workbench Migration Wizard FAQ
A MySQL Workbench Frequently Asked Questions
B Keyboard Shortcuts
C Extending Workbench
C.1 GRT and Workbench Data Organization
C.2 Modules
C.3 Plugins and Tools
C.4 Adding a GUI to a Plugin Using MForms
C.5 The Workbench Scripting Shell
C.5.1 Exploring the Workbench Scripting Shell
C.5.2 The Shell Window
C.5.3 Files, Globals, Classes, Modules, and Notifications Tabs
C.6 Tutorial: Writing Plugins
C.6.1 Tutorial: Generate PHP Code to Create a Connection with PDO_MySQL
C.6.2 Tutorial: Generating Foreign Keys with MyISAM
D How To Report Bugs or Problems
E MySQL Enterprise Features
F MySQL Utilities
  1. 5.1.641.0: February 26, 2014: Delta from last release: This is just a patch release to the last 5.1.636.0 build to include the following bug fixes. Bug fix for some potential calibration issue with trace features in CCS. Bug fix for the flash erase issue with CC13xx/CC26xx devices when using XDS110 emulator in CCS. Windows; Linux; 5.1.636.0.
  2. 3133750 SQL Server 2012 SP3 build versions: SQL Server 2008 R2: 2527041 How to obtain the latest service pack for SQL Server 2008 R2: 981356 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was released 2567616 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 1 was released.
  3. Vi Notices Federal Communications Commission Statement This device complies with Part 15 of the FCC Rules. Operation is subject to the following two.
Details
Written by Nam Ha Minh
Last Updated on 02 September 2019 | Print Email
PicPick Serial Key is a full-featured screen capture app for Windows, intuitive image editor, color picker, color palette, pixel-ruler, protractor, crosshair, whiteboard and more. User friendly and full of features for creating your image, suitable for software developers, graphic designers and home users. Microsoft SQL documentation. Learn how to use SQL Server and Azure SQL to manage your database needs, both on-premises and in the cloud. (quick link: https://aka.ms/sqldocs).
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 in a MySQL database called 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
Sql Front 5.1 Build 4.16 Serial
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 with following details: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.
Build

Sql Front 5.1 Build 4.16 Serial Online

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 - [email protected]

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

Sql Front 5.1 Build 4.16 Serial Idm

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:

Sql Front 5.1 Build 4.16 Serial Key

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.

Sql Front 5.1 Build 4.16 Serial Download

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