CODEWITHSUNDEEP
javadatabasejdbcxampp
Jeiman
Jeiman

Reputation: 1181

How to connect to database connection in Java

I would like to know how to connect to database in which is hosted in Xampp MySQL.

This is what I have so far in my java code to connect but I'm unsure what I'm doing. 

public static void main(String[] args) {
    try {
        Connection con = DriverManager.getConnection( host, username, password );
        String host = "jdbc:derby://localhost:1527/Employees";
        String uName = "root";
        String uPass= "password";
    }
    catch ( SQLException err ) {
    System.out.println( err.getMessage( ) );
    }

}
  1. What would the host URL be?
  2. Do i need a JDBC Jar file to connect to the DB?

I have setup a database and table via phpMyAdmin already. Just don't know how to proceed.

I am using Netbeans to write my Java code to connect to a local database which was created via Xampp PHPMyAdmin.

In the end, I want to create a database connection in Java and call out tables within the IDE. Would appreciate some help.

Upvotes: 5

Views: 27560

Answers (8)

Faisal shahzad
Faisal shahzad

Reputation: 398

Database connectivity: these are the steps required to connect Java application to a database.

  • Import the sql package

    Import.java.sql.*;

  • Load driver

    Every database has different driver, we are using SQL database so driver for SQL database is

    Class.forName(“com.mysql.jdbc.Driver”);

  • Make Url

    String url=”jdbc:mysql://localhost/DataBaseName”;

  • Make connection

    Connection con=DriverManager(url,”root”,””);

  • Create statement object

    Statement st = con.CreateStatement();

    After creating statement object, we can perform SQL queries on the database.

  • Make query

    String sql = "SELECT * FROM table_Name ";

  • Create ResultSet object and execute query

    ResultSet rs = st.excuteUpdate(sql);

  • Important step to close the connection

    con.close();

Note if we do not have SQL connecter in jdk first download connector from this url:

https://dev.mysql.com/downloads/connector/j/5.1.html

and copy and paste into:

Java/jre/lib/ext

Upvotes: 0

Lasan Rashmika
Lasan Rashmika

Reputation: 19

Use the below code. This will work

import java.sql.*;  
class MysqlCon{  
public static void main(String args[]){  
try{  
Class.forName("com.mysql.jdbc.Driver");  
Connection con=DriverManager.getConnection(  
"jdbc:mysql://localhost:3306/sonoo","root","root");  
//here is database name, root is username and password  
Statement stmt=con.createStatement();  
ResultSet rs=stmt.executeQuery("select * from emp");  
while(rs.next())  
System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));  
con.close();  
}catch(Exception e){ System.out.println(e);}  
}  
}

Upvotes: -1

user16830953
user16830953

Reputation: 1

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

public class DbOperations {
  Connection con;
  PreparedStatement ps;
    public DbOperations() {
        con = DbUtil.getConnection();
    }
    public boolean insertSubject(String name) throws SQLException {
    Statement s = con.createStatement();
    String sql = "insert into subject (name) values ('"+ name +"')";
     ps=con.prepareStatement(sql);
// ps.executeUpdate();
    ps.executeUpdate();
    if(ps!= null){
        System.out.println("successfull!!");
      return true;
    }else {
    return false;
    }
  
    }
    public ArrayList getSubjectById(int id) throws SQLException {
   
     Statement s = con.createStatement();
    String sql = "select * from subject where id = '"+ id +"'";
     ResultSet resultSet = s.executeQuery(sql);
   ArrayList<Object> records=new ArrayList<Object>();
while(resultSet.next()){
    // subject sub = new subject();
    // sub.id = resultSet.getInt(1);
    // sub.name = resultSet.getString(2);
    // records.add(sub.id);
    // records.add(sub.name);
    records.add(resultSet.getInt(1));
    records.add(resultSet.getString(2));
    System.out.println("record output:" + records );
}

return (ArrayList) records;
    }

    public ResultSet getAllSubjects() throws SQLException {
 Statement s = con.createStatement();
    String sql = "select * from subject";
     ResultSet resultSet = s.executeQuery(sql);
        return resultSet;
  }
  
    public boolean insertStudent(String student_name, float score, String name) throws SQLException {

    Statement s = con.createStatement();
    String sql = "select id from subject where name = '"+ name +"'";
     ResultSet resultSet = s.executeQuery(sql);
     int subId =0;
    while(resultSet.next()){
      subId = resultSet.getInt(1);
    }

    String sql1 = "insert into student (student_name,score,subject_id) values ('"+ student_name+"','"+score+"','"+subId+"')";

    int result = s.executeUpdate(sql1);
    if(result==0){
      return false;
    }else {
        return true;
    }
    
    }
    public ArrayList getStudentyId(int id) throws SQLException {

Statement s = con.createStatement();
    String sql = "select * from student where id = '"+ id +"'";
     ResultSet resultSet = s.executeQuery(sql);
   ArrayList<Object> records=new ArrayList<Object>();
while(resultSet.next()){
    // student sub = new student();
    // sub.id = resultSet.getInt(1);
    // sub.student_name = resultSet.getString(2);
    // sub.score = resultSet.getFloat(3);
    // sub.subject_id = resultSet.getInt(4);

    // records.add(sub.id);
    // records.add(sub.student_name);
    // records.add(sub.score);
    // records.add(sub.subject_id);
    records.add(resultSet.getInt(1));
    records.add( resultSet.getString(2));
    records.add(resultSet.getFloat(3));
    records.add(resultSet.getInt(4));

}
return (ArrayList) records;

}
    public ResultSet getAllStudents() throws SQLException {
        Statement s = con.createStatement();
    String sql = "select * from student";
     ResultSet resultSet = s.executeQuery(sql);
        return resultSet;
    }
    
}

Upvotes: 0

Mike
Mike

Reputation: 1

Import the packages: Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.

Register the JDBC driver: Requires that you initialize a driver so you can open a communication channel with the database.

Open a connection: Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with the database.

Upvotes: 0

Deepak Kumar
Deepak Kumar

Reputation: 1

package com.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;



public class DatabaseUtil1 {

    public static final String DRIVER="oracle.jdbc.driver.OracleDriver";
    public static final String URL="jdbc:oracle:thin:@DIPAK-PC:1521:xe";
    public static final String USERNAME="system";
    public static final String PASSWORD="password";

    public static Connection getConnection() throws ClassNotFoundException, SQLException
    {
        Connection con= null;

        Class.forName(DRIVER);
        con=DriverManager.getConnection(URL,USERNAME,PASSWORD);

        System.out.println(con);
        return con; 
    }
    public static void closePreparedStatement(PreparedStatement pst) throws SQLException
    {
        if(pst!=null)
        {
            pst.close();
        }

    }
    public static void closeConnection(Connection con) throws SQLException
    {
        if(con!=null)
        {
            con.close();
        }

    }



}

Upvotes: 0

Ashot Karakhanyan
Ashot Karakhanyan

Reputation: 2830

This is a code (Java 7 style, try-with-resources, more laconic style) to connect and retrieve data from your DB. 

 public static final String SELECT_QUERY = "SELECT * FROM your_table_name";
 public static void main(String[] args) {
    String host = "jdbc:derby://localhost:1527/Employees";
    String uName = "root";
    String uPass = "password";

    try (Connection conn = DriverManager.getConnection(host, uName, uPass);
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(SELECT_QUERY)) {

        while (rs.next()) {
            //read your lines one ofter one
            int id = rs.getInt("id");
            String somePropertyValue = rs.getInt("some_column_name");
            // etc. 
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Also, add JDBC driver (*.jar file) in your classpath if you are running from command line, or add this jar to your project, if you are working in IDE (Eclipse, IDEA etc. It is a little bit different for each one).

BTW, How your code compiled if variable declaration is after they using? That code can not be compiled even.

Upvotes: 2

Suganthan Madhavan Pillai
Suganthan Madhavan Pillai

Reputation: 5655

This is the project structure

Project Structure

Try with this

Update

    public static void main(String[] args) {
    // TODO Auto-generated method stub
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        System.out.println("Where is your MySQL JDBC Driver?");
        e.printStackTrace();
        return;
    }

    System.out.println("MySQL JDBC Driver Registered!");
    Connection connection = null;

    try {
        connection = DriverManager
        .getConnection("jdbc:mysql://localhost:3306/testspring","root", "password");

    } catch (SQLException e) {
        System.out.println("Connection Failed! Check output console");
        e.printStackTrace();
        return;
    }

    if (connection != null) {
        System.out.println("You made it, take control your database now!");
    } else {
        System.out.println("Failed to make connection!");
    }
}

It is working for me

I downloaded jar from Java2s.com

Refer

Upvotes: 7

Happy
Happy

Reputation: 1855

Maybe you should define your strings before use them.

The host URL seems good.

You have to install the driver in your server, or to put it in the /lib folder of your project. You fill find it in %DERBY_HOME%/lib folder, named derby.jar, assuming %DERBY_HOME% is the directory installation of Derby.

Upvotes: 0

Related Questions