Connect Java to a MySQL database

Question

How do you connect to a MySQL database in Java   When I try  I get   java sql SQLException  No suitable driver found for jdbc mysql   database table     at java sql DriverManager getConnection DriverManager java 689      at java sql DriverManager getConnection DriverManager java 247    Or   java lang ClassNotFoundException  com mysql jdbc Driver   Or  java lang ClassNotFoundException  com mysql cj jdbc Driver

User · Answer

String url    jdbc mysql   127 0 0 1 3306 yourdatabase   String user    username   String password    password       Load the Connector J driver Class forName  com mysql jdbc Driver   newInstance       Establish connection to MySQL Connection conn   DriverManager getConnection url  user  password

User · Answer

Here s the very minimum you need to get data out of a MySQL database   Class forName  com mysql jdbc Driver   newInstance    Connection conn   DriverManager getConnection      jdbc mysql   localhost 3306 foo    root    password     Statement stmt   conn createStatement    stmt execute  SELECT   FROM  FOO BAR     stmt close    conn close      Add exception handling  configuration etc  to taste

User · Answer

Download JDBC Driver  Download link  Select platform independent   https   dev mysql com downloads connector j   Move JDBC Driver to C Drive   Unzip the files and move to C   drive  Your driver path should be like C  mysql-connector-java-8 0 19 mysql-connector-java-8 0 19  Run Your Java  java -cp  C  mysql-connector-java-8 0 19 mysql-connector-java-8 0 19 mysql-connector-java-8 0 19 jar  testMySQL java   testMySQL java  import java sql    import java io     public class testMySQL       public static void main String   args           TODO Auto-generated method stub         try                         Class forName  com mysql cj jdbc Driver                  Connection con DriverManager getConnection                     jdbc mysql   localhost 3306 db useSSL false amp useUnicode true amp useJDBCCompliantTimezoneShift true amp useLegacyDatetimeCode false amp serverTimezone UTC   root                     Statement stmt con createStatement                  ResultSet rs stmt executeQuery  show databases                   System out println  Connected                        catch Exception e                        System out println e

User · Answer

Short Code  public class DB        public static Connection c       public static Connection getConnection   throws Exception           if  c    null                Class forName  com mysql jdbc Driver                c  DriverManager getConnection  jdbc mysql   localhost 3306 DATABASE    USERNAME    Password                      return c                Send data TO Database     public static void setData String sql  throws Exception           DB getConnection   createStatement   executeUpdate sql                 Get Data From Database     public static ResultSet getData String sql  throws Exception           ResultSet rs   DB getConnection   createStatement   executeQuery sql           return rs

User · Answer

DriverManager is a fairly old way of doing things  The better way is to get a DataSource  either by looking one up that your app server container already configured for you   Context context   new InitialContext    DataSource dataSource    DataSource  context lookup  java comp env jdbc myDB      or instantiating and configuring one from your database driver directly   MysqlDataSource dataSource   new MysqlDataSource    dataSource setUser  scott    dataSource setPassword  tiger    dataSource setServerName  myDBHost example org      and then obtain connections from it  same as above   Connection conn   dataSource getConnection    Statement stmt   conn createStatement    ResultSet rs   stmt executeQuery  SELECT ID FROM USERS        rs close    stmt close    conn close

User · Answer

Initialize database constants  Create constant properties database username  password  URL and drivers  polling limit etc      init database constants    com mysql jdbc Driver private static final String DATABASE DRIVER    com mysql cj jdbc Driver   private static final String DATABASE URL    jdbc mysql   localhost 3306 database name   private static final String USERNAME    root   private static final String PASSWORD       private static final String MAX POOL    250      set your own limit   Initialize Connection and Properties  Once the connection is established  it is better to store for reuse purpose      init connection object private Connection connection     init properties object private Properties properties    Create Properties  The properties object hold the connection information  check if it is already set      create properties private Properties getProperties         if  properties    null            properties   new Properties            properties setProperty  user   USERNAME           properties setProperty  password   PASSWORD           properties setProperty  MaxPooledStatements   MAX POOL             return properties      Connect the Database  Now connect to database using the constants and properties initialized      connect database public Connection connect         if  connection    null            try               Class forName DATABASE DRIVER               connection   DriverManager getConnection DATABASE URL  getProperties               catch  ClassNotFoundException   SQLException e                   Java 7              e printStackTrace                        return connection      Disconnect the database  Once you are done with database operations  just close the connection      disconnect database public void disconnect         if  connection    null            try               connection close                connection   null            catch  SQLException e                e printStackTrace                        Everything together  Use this class MysqlConnect directly after changing database name  username and password etc   import java sql Connection  import java sql DriverManager  import java sql SQLException  import java util Properties   public class MysqlConnect          init database constants     private static final String DATABASE DRIVER    com mysql cj jdbc Driver       private static final String DATABASE URL    jdbc mysql   localhost 3306 database name       private static final String USERNAME    root       private static final String PASSWORD           private static final String MAX POOL    250           init connection object     private Connection connection         init properties object     private Properties properties          create properties     private Properties getProperties             if  properties    null                properties   new Properties                properties setProperty  user   USERNAME               properties setProperty  password   PASSWORD               properties setProperty  MaxPooledStatements   MAX POOL                     return properties                connect database     public Connection connect             if  connection    null                try                   Class forName DATABASE DRIVER                   connection   DriverManager getConnection DATABASE URL  getProperties                   catch  ClassNotFoundException   SQLException e                    e printStackTrace                                    return connection                disconnect database     public void disconnect             if  connection    null                try                   connection close                    connection   null                catch  SQLException e                    e printStackTrace                                      How to Use   Initialize the database class         note    this is just init    it will not create a connection MysqlConnect mysqlConnect   new MysqlConnect      Somewhere else in your code      String sql    SELECT   FROM  stackoverflow    try       PreparedStatement statement   mysqlConnect connect   prepareStatement sql           go on             go on             DONE        catch  SQLException e        e printStackTrace      finally       mysqlConnect disconnect        This is all    If anything to improve edit it  Hope this is helpful

User · Answer

Here s a step by step explanation how to install MySQL and JDBC and how to use it    Download and install the MySQL server  Just do it the usual way  Remember the port number whenever you ve changed it  It s by default 3306  Download the JDBC driver and put in classpath  extract the ZIP file and put the containing JAR file in the classpath  The vendor-specific JDBC driver is a concrete implementation of the JDBC API  tutorial here     If you re using an IDE like Eclipse or Netbeans  then you can add it to the classpath by adding the JAR file as Library to the Build Path in project s properties    If you re doing it  plain vanilla  in the command console  then you need to specify the path to the JAR file in the -cp or -classpath argument when executing your Java application   java -cp    path to mysql-connector jar com example YourClass  The   is just there to add the current directory to the classpath as well so that it can locate com example YourClass and the   is the classpath separator as it is in Windows  In Unix and clones   should be used  Create a database in MySQL  Let s create a database javabase  You of course want World Domination  so let s use UTF-8 as well   CREATE DATABASE javabase DEFAULT CHARACTER SET utf8 COLLATE utf8 unicode ci   Create an user for Java and grant it access  Simply because using root is a bad practice   CREATE USER  java   localhost  IDENTIFIED BY  password   GRANT ALL ON javabase   TO  java   localhost  IDENTIFIED BY  password     Yes  java is the username and password is the password here  Determine the JDBC URL  To connect the MySQL database using Java you need an JDBC URL in the following syntax   jdbc mysql   hostname port databasename   hostname  The hostname where MySQL server is installed  If it s installed at the same machine where you run the Java code  then you can just use localhost  It can also be an IP address like 127 0 0 1  If you encounter connectivity problems and using 127 0 0 1 instead of localhost solved it  then you ve a problem in your network DNS hosts config  port  The TCP IP port where MySQL server listens on  This is by default 3306  databasename  The name of the database you d like to connect to  That s javabase    So the final URL should look like   jdbc mysql   localhost 3306 javabase Test the connection to MySQL using Java  Create a simple Java class with a main   method to test the connection    String url    jdbc mysql   localhost 3306 javabase   String username    java   String password    password    System out println  Connecting database        try  Connection connection   DriverManager getConnection url  username  password         System out println  Database connected       catch  SQLException e        throw new IllegalStateException  Cannot connect the database    e       If you get a SQLException  No suitable driver  then it means that either the JDBC driver wasn t autoloaded at all or that the JDBC URL is wrong  i e  it wasn t recognized by any of the loaded drivers   Normally  a JDBC 4 0 driver should be autoloaded when you just drop it in runtime classpath  To exclude one and other  you can always manually load it as below   System out println  Loading driver        try       Class forName  com mysql jdbc Driver        System out println  Driver loaded       catch  ClassNotFoundException e        throw new IllegalStateException  Cannot find the driver in the classpath    e       Note that the newInstance   call is not needed here  It s just to fix the old and buggy org gjt mm mysql Driver  Explanation here  If this line throws ClassNotFoundException  then the JAR file containing the JDBC driver class is simply not been placed in the classpath   Note that you don t need to load the driver everytime before connecting  Just only once during application startup is enough   If you get a SQLException  Connection refused or Connection timed out or a MySQL specific CommunicationsException   Communications link failure  then it means that the DB isn t reachable at all  This can have one or more of the following causes    IP address or hostname in JDBC URL is wrong  Hostname in JDBC URL is not recognized by local DNS server  Port number is missing or wrong in JDBC URL  DB server is down  DB server doesn t accept TCP IP connections  DB server has run out of connections  Something in between Java and DB is blocking connections  e g  a firewall or proxy     To solve the one or the other  follow the following advices    Verify and test them with ping  Refresh DNS or use IP address in JDBC URL instead  Verify it based on my cnf of MySQL DB  Start the DB  Verify if mysqld is started without the --skip-networking option  Restart the DB and fix your code accordingly that it closes connections in finally  Disable firewall and or configure firewall proxy to allow forward the port     Note that closing the Connection is extremely important  If you don t close connections and keep getting a lot of them in a short time  then the database may run out of connections and your application may break  Always acquire the Connection in a try-with-resources statement  Or if you re not on Java 7 yet  explicitly close it in finally of a try-finally block  Closing in finally is just to ensure that it get closed as well in case of an exception  This also applies to Statement  PreparedStatement and ResultSet    That was it as far the connectivity concerns  You can find here a more advanced tutorial how to load and store fullworthy Java model objects in a database with help of a basic DAO class     Using a Singleton Pattern for the DB connection is a bad approach  See among other questions  http   stackoverflow com q 9428573   This is a  1 starters mistake

User · Answer

MySQL JDBC Connection with useSSL    private String db server   BaseMethods getSystemData  db server    private String db user   BaseMethods getSystemData  db user    private String db password   BaseMethods getSystemData  db password     private String connectToDb   throws Exception      String jdbcDriver    com mysql jdbc Driver      String dbUrl    jdbc mysql       db server              verifyServerCertificate false              amp useSSL true              amp requireSSL true       System setProperty jdbcDriver           Class forName jdbcDriver  newInstance         Connection conn   DriverManager getConnection dbUrl  db user  db password       Statement statement   conn createStatement        String query    SELECT EXTERNAL ID FROM offer letter where ID             letterID             ResultSet resultSet   statement executeQuery query       resultSet next        return resultSet getString 1

User · Answer

HOW   To set up the Driver to run a quick sample   1  Go to https   dev mysql com downloads connector j   get the latest version of Connector J  2  Remember to set the classpath to include the path of the connector jar file  If we don t set it correctly  below errors can occur   No suitable driver found for jdbc mysql   127 0 0 1 3306 msystem development  java lang ClassNotFoundException  com mysql jdbc Driver    To set up the CLASSPATH   Method 1  set the CLASSPATH variable   export CLASSPATH    mysql-connector-java-VERSION jar  java MyClassFile   In the above command  I have set the CLASSPATH to the current folder and mysql-connector-java-VERSION jar file  So when the java MyClassFile command executed  java application launcher will try to load all the Java class in CLASSPATH  And it found the Drive class    BOOM errors was gone   Method 2   java -cp   mysql-connector-java-VERSION jar MyClassFile   Note  Class forName  com mysql jdbc Driver    This is deprecated at this moment 2019 Apr   Hope this can help someone

User · Answer

you need to have mysql connector jar in your classpath    in Java JDBC API makes everything with databases  using JDBC  we can write Java applications to 1  Send queries or  update SQL to DB any relational Database   2  Retrieve and process the results from DB  with below three steps we can able to retrieve data from any  Database  Connection con   DriverManager getConnection                        jdbc myDriver DatabaseName                        dBuserName                       dBuserPassword    Statement stmt   con createStatement    ResultSet rs   stmt executeQuery  SELECT a  b  c FROM Table     while  rs next          int x   rs getInt  a        String s   rs getString  b        float f   rs getFloat  c

User · Answer

MySql JDBC Connection   Class forName  com mysql jdbc Driver          Connection con DriverManager getConnection  jdbc mysql   localhost 3306 DatabaseName   Username   Password             Statement stmt con createStatement                stmt   con createStatement    ResultSet rs stmt executeQuery  Select   from Table

User · Answer

Short and Sweet code   try              Class forName  com mysql jdbc Driver        System out println  Driver Loaded        conn   DriverManager getConnection  jdbc mysql   localhost 3306 testDB   root             Database Name - testDB  Username -  root   Password -        System out println  Connected                  catch Exception e        e printStackTrace        For SQL server 2012  try       String url    jdbc sqlserver   KHILAN 1433 databaseName testDB user Khilan password Tuxedo123          KHILAN is Host    and 1433 is port number          Class forName  com microsoft sqlserver jdbc SQLServerDriver        System out println  Driver Loaded        conn   DriverManager getConnection url       System out println  Connected         catch Exception e        e printStackTrace

User · Answer

Connection I was using some time ago  it was looking like the easiest way  but also there were recommendation to make there if statement- exactly   Connection con   DriverManager getConnection                        jdbc myDriver DatabaseName                        dBuserName                       dBuserPassword   if  con    null        handle your code there      Or something like in that way     Probably there s some case  while getConnection can return null

User · Answer

You can see all steps to connect MySQL database from Java application here  For other database  you just need to change the driver in first step only  Please make sure that you provide right path to database and correct username and password   Visit http   apekshit com t 51 Steps-to-connect-Database-using-JAVA

[java] Connect Java to a MySQL database

Examples related to java

Examples related to mysql

Examples related to jdbc