{"id":1233,"date":"2017-06-21T16:40:56","date_gmt":"2017-06-21T16:40:56","guid":{"rendered":"http:\/\/www.testingdocs.com\/questions\/?p=1233"},"modified":"2024-09-04T15:38:35","modified_gmt":"2024-09-04T15:38:35","slug":"write-a-jdbc-program-to-connect-mysql-database","status":"publish","type":"post","link":"https:\/\/www.testingdocs.com\/questions\/write-a-jdbc-program-to-connect-mysql-database\/","title":{"rendered":"Write a JDBC program to connect MySQL Database?"},"content":{"rendered":"

Write a JDBC program to connect MySQL Database?<\/h1>\n

JDBC stands for Java DataBase Connectivity. It is an API for Java to connect to various databases. Before writing the program to connect to MySQL database, we will do some initial setup in the back-end.<\/p>\n

JDBC API<\/h2>\n

 <\/p>\n

\"JDBC<\/p>\n

<\/h3>\n

MySQL Database Setup<\/strong><\/h3>\n

We will create a sample database and an employee table in the back-end MySQL database.<\/p>\n

Connect to MySQL and Create database command as shown below:<\/p>\n

CREATE DATABASE testingdocs;<\/p>\n

\"Create<\/p>\n

Table creation:<\/strong><\/h3>\n
CREATE TABLE IF NOT EXISTS employee (\r\n emp_id INT(10) NOT NULL AUTO_INCREMENT,\r\n name VARCHAR(45) DEFAULT NULL,\r\n salary INT(10) DEFAULT NULL,\r\n dept VARCHAR(100) DEFAULT NULL,\r\n PRIMARY KEY (emp_id)\r\n) ENGINE=InnoDB<\/pre>\n
 <\/p>\n
\"Create<\/p>\n
 <\/p>\n
Populate the table with sample data as shown in the picture.<\/p>\n
INSERT INTO employee VALUES(2,”Surendra Kumar”,10000,”Quality Assurance”);
\nINSERT INTO employee VALUES(2,”Regan M”,18000,”Product Development”);<\/p>\n
 <\/p>\n
\"Insert<\/p>\n
MySQL Connector\/J Maven dependency<\/strong><\/h3>\n
To connect to MySQL database, I have used\u00a0 MySQL Connector\/J. It is a JDBC Type 4 driver for communicating with MySQL servers. Maven dependency of the connector version 6.xx is below. Place this dependency in your project pom.xml and update your project.<\/p>\n
<\/pre>\n
<!-- https:\/\/mvnrepository.com\/artifact\/mysql\/mysql-connector-java --> \r\n\r\n<dependency>\r\n<groupId>mysql<\/groupId>\r\n<artifactId>mysql-connector-java<\/artifactId>\r\n<version>6.0.6<\/version>\r\n<\/dependency><\/pre>\n
 <\/p>\n
Building the database url for MySQL is as follows:<\/p>\n
jdbc:mysql:\/\/<domain_where_mysql_server_runs>\/<database_name_to_connect><\/p>\n
or<\/p>\n
jdbc:mysql:\/\/<HOST>:<PORT>\/<database_name_to_connect><\/p>\n
example:<\/p>\n
jdbc:mysql:\/\/127.0.0.1:3306\/<database_name_to_connect><\/p>\n
Retrieve data from MySQL<\/strong><\/h2>\n
<\/pre>\n
import java.sql.*;\r\n\r\npublic class JDBCMySQLExample {\r\n    public static void main(String args[]){\r\n        String dbEmpID;\r\n        String dbEmpName;\r\n        String dbSalary;\r\n        String dbMySQLUrl = \"jdbc:mysql:\/\/localhost\/testingdocs?user=root&amp;password=\";\r\n        String sqlQuery = \"SELECT emp_id , name , salary FROM employee \";\r\n        try {\r\n            Connection con = DriverManager.getConnection (dbMySQLUrl);\r\n            Statement stmt = con.createStatement();\r\n            ResultSet rs = stmt.executeQuery(sqlQuery);\r\n\r\n            while (rs.next()) {\r\n                dbEmpID = rs.getString(1);\r\n                dbEmpName = rs.getString(2);\r\n                dbSalary = rs.getString(3);\r\n                System.out.println(\"Employee ID :\"+ dbEmpID);\r\n                System.out.println(\"Employee Name :\"+ dbEmpName);\r\n                System.out.println(\"Employee Salary :\"+ dbSalary);\r\n                System.out.println(\"***************\");\r\n            }\r\n            con.close();\r\n        }\r\n        catch(SQLException e) {\r\n            e.printStackTrace();\r\n        }\r\n\r\n    }\r\n\r\n}<\/pre>\n
<\/pre>\n
Run the program to display employee name and salary from the DB.<\/p>\n
 <\/p>\n
\"MySQL<\/p>\n","protected":false},"excerpt":{"rendered":"
Write a JDBC program to connect MySQL Database? JDBC stands for Java DataBase Connectivity. It is an API for Java to connect to various databases. Before writing the program to connect to MySQL database, we will do some initial setup in the back-end. JDBC API   MySQL Database Setup We will create a sample database […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[],"class_list":["post-1233","post","type-post","status-publish","format-standard","hentry","category-java","has-post-title","has-post-date","has-post-category","has-post-tag","has-post-comment","has-post-author",""],"_links":{"self":[{"href":"https:\/\/www.testingdocs.com\/questions\/wp-json\/wp\/v2\/posts\/1233","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.testingdocs.com\/questions\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.testingdocs.com\/questions\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.testingdocs.com\/questions\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.testingdocs.com\/questions\/wp-json\/wp\/v2\/comments?post=1233"}],"version-history":[{"count":9,"href":"https:\/\/www.testingdocs.com\/questions\/wp-json\/wp\/v2\/posts\/1233\/revisions"}],"predecessor-version":[{"id":24183,"href":"https:\/\/www.testingdocs.com\/questions\/wp-json\/wp\/v2\/posts\/1233\/revisions\/24183"}],"wp:attachment":[{"href":"https:\/\/www.testingdocs.com\/questions\/wp-json\/wp\/v2\/media?parent=1233"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.testingdocs.com\/questions\/wp-json\/wp\/v2\/categories?post=1233"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.testingdocs.com\/questions\/wp-json\/wp\/v2\/tags?post=1233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}