Access to SQL file from Java and DriverManager

Question

Access to SQL file from Java and DriverManager

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I have a database file SQL that I want to access from a Java class that makes the connection to DriverManager and JDBC , but I'm a bit lost.

the file is called items.sql

DBItem.java

package beans;

import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author elcer
 */
public class DBItem {
    private String driver = "com.mysql.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/items";

    private Connection conn = null;

    public DBItem() { //el constructor crea y abre la conexion
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, "usuario", "password");
            if (conn != null) 
                System.out.println("Conexion efectuada con exito");                    
        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger
                (DBItem.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public LinkedList<Item> getItems(String tipo) {
        LinkedList<Item> itemsTipo = new LinkedList<>();
        if (conn != null) {
            try (Statement st = conn.createStatement()) {
                String sqls = "SELECT * from items WHERE tipo='" + tipo + "'"; 
                try (ResultSet rs = st.executeQuery(sqls)) {
                    while (rs.next()) {
                        String nombre = rs.getString("nombre");
                        Item item = new Item(nombre, tipo);
                        itemTipo.add(item);
                    }
                }
            } catch (SQLException ex) {
                Logger.getLogger(DBItem.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return itemsTipo;
    }

    public void closeDB() {
        try {
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DBItem.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

I try to access the database, but it does not recognize me, it tells me that it can not be initialized and such ... Can someone help me?

sql java

asked by Álvaro Rojas 23.03.2017 в 09:47

source

2 answers

WIA 2.0 Scan documents both sides in Java Next and previous buttons in form / php query

score 0 · Answer 1

DriverManager allows you to connect to a Database. If what you have is a file with SQL code (I understand that it would be a flat text file, with the queries to create tables, inserts, etc., that you can open and read, without using Java) to access them from Java as you want to do first they have to be in the Database. If you are not, you can use DriverManager and JDBC to enter them, after read the contents of the file and prepare the data in the way that suits you.

Greetings.

score 0 · Answer 2

The class that you have put what it does is open a connection (in the constructor) and provide a method to retrieve a list of items, of a certain type such as LinkedList.

Now, suppose you already have the DB created, where the items table exists, where you already have records. With this class you can recover part of those records.

If your SQL file only has insertion queries of valid records, you can read it (If you should create a class for this, using File, FileInputStream, etc.), dump the read content in a string, and then, supposing that what you have a querie of insertion, open the connection with the DB, create a Statement and use executeUpdate () on it to store the data. A method to insert a record for the class you already have could be this:

public void insert( String sql_insert ) {
            System.out.println( "Ejecutando sentencia SQL..." );

            try ( Statement st = this.conn.createStatement( )) {
                System.out.println( "Statement creado !" );

                st.executeUpdate( arr_sql[ i ] );


                System.out.println( "Cerrando conexión..." );
                st.close();

            } catch( SQLException e ) {

                System.out.println( "Error realizando insert." );
                System.out.println( "---> " + e.toString( ));
            }
        }

If you do not understand anything of what I have told you, you have a lot to study, look at some simple tutorial, for example, this: link