Вывод данных из базы в динамическую таблицу

#java #sql_server #jdbc

                    
Имеется вот такая примерно таблица - 

      
  
          
                
     
        
           #{msgs.customerIdHeader}
           #{customer.id}
        
        
           #{msgs.NDOKHeader}
           #{customer.NDOK}
        
                     
           #{msgs.SODRABHeader}
           #{customer.SODRAB}
        
     
       


Как написать правильный код с использованием jdbc, который бы подключался к базе
и получал данные, а затем они попадали в данную таблицу?
    

Ответы

Ответ 1

1) Для подключения к MSSQL вам поднадобится соответствующий драйвер. В Maven Central
его нету, поэтому вам будет нужно самостоятельно добыть внутри каталога установки драйвера:

\sqljdbc_\\sqljdbc.jar
\sqljdbc_\\sqljdbc4.jar
\sqljdbc_\\sqljdbc41.jar
\sqljdbc_\\sqljdbc42.jar


согласно документации Microsoft.

Скачать драйвер можно с сайта Microsoft.

(Поясню на всякий случай: installation directory не надо никуда прописывать - это
просто путь до драйвера при обычной установке из инсталлятора. Если у вас уже все скачано,
достаточно добавить драйвер в CLASSPATH.)

Далее этот драйвер нужно добавить в CLASSPATH любым методом, специфичным для вашего
проекта. Например, если вы собираете вручную из консоли, то это будет нечто вроде:

CLASSPATH =.;C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_4.2\enu\sqljdbc42.jar

CLASSPATH =.:/home/usr1/mssqlserverjdbc/Driver/sqljdbc_4.2/enu/sqljdbc42.jar


Если вы используете IntelliJ IDEA, то это делается так:


нужно зайти в настройки проекта (File->Project Structure)
в разделе Modules найти ваш модуль
на вкладке Dependencies в самом низу нажать на плюсик
выбрать "JARs or Directories"
добавить нужную директорию


Если используется Maven, то есть проблема, в Maven Central нету этого артефакта,
то есть его придется оформить самостоятельно.

Для серьезных больших проектов, самый правильный способ - поднять собственный репозиторий.
На выбор, например, есть Artifactory и Nexus. Как работать с этими инструментами -
описывать слишком долго, не влезет в максимальный размер ответа. Скорей всего, у вас
не тот случай, но я был обязан предупредить.

Для небольших проектов, можно установить нужный jar в локальный репозиторий Maven.
Про это на сайте Maven есть специальная документация. Вкратце, достаточно просто запустить
в терминале строчку:

mvn install:install-file -Dfile=<путь до джарки с жрайвером без угловых скобочек>
-DgroupId=com.microsoft.sqlserver \
    -DartifactId=com.microsoft.sqlserver.jdbc.SQLServerDriver -Dversion=4.2 -Dpackaging=jar


и далее добавить в pom.xml новую зависимость вида:


    
        com.microsoft.sqlserver
        com.microsoft.sqlserver.jdbc.SQLServerDriver
        4.2
    



В общем, добавление новой джарки с сборку проекта очень зависит от характеристик
вашего проекта, способов сборки, деплоймента, итп. Тут все каждый решает для себя.

2) Далее у нас есть допустим Entity класс:

public class Customer {

    private Integer id;
    private String NDOK;
    private String SODRAB;

    public Customer() {
    }

    public Customer(Integer id, String NDOK, String SODRAB) {
        this.id = id;
        this.NDOK = NDOK;
        this.SODRAB = SODRAB;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getNDOK() {
        return NDOK;
    }

    public void setNDOK(String NDOK) {
        this.NDOK = NDOK;
    }

    public String getSODRAB() {
        return SODRAB;
    }

    public void setSODRAB(String SODRAB) {
        this.SODRAB = SODRAB;
    }
}


3) Далее создаем managed bean:

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;

@ManagedBean
@SessionScoped
public class CustomerBean implements Serializable {

    private static final long serialVersionUID = 6081417964063918994L;

    public List getCustomers() throws ClassNotFoundException, SQLException {

        Connection connect = null;

        //Привел пример, как указать еще и в url, это необязательно
        String url = "jdbc:sqlserver://localhost:1433;" +
     "databaseName=CustomerDatabase;user=user;password=password";

        String username = "user";
        String password = "password";

        try {

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            connect = DriverManager.getConnection(url, username, password);
            // System.out.println("Connection established"+connect);

        } catch (SQLException ex) {
            System.out.println("in exec");
            System.out.println(ex.getMessage());
        }

        List customers = new ArrayList();
        PreparedStatement pstmt = connect
                .prepareStatement("select id, NDOK, SODRAB from Customer");
        ResultSet rs = pstmt.executeQuery();

        while (rs.next()) {

            Customer customer = new Customer();
            customer.setId(rs.getInt("id"));
            customer.setNDOK(rs.getString("NDOK"));
            customer.setSODRAB(rs.getString("SODRAB"));

            customers.add(customer);

        }

        // close resources
        rs.close();
        pstmt.close();
        connect.close();

        return customers;

    }

}


4) Передаете данные в таблицу способом, описанным в стартовом посте.

5) PROFIT

Как занести данные из HTML-формы в MySQL при помощи JDBC?

#html #mysql #jdbc

Имеются 2 HTML-формы 

 


и 




База данных на MySQL Server, где присутствуют аналогичные поля login varchar (45)
NOT NULL и password varchar (45) NOT NULL. 

Подскажите, как при помощи JDBC записать данные HTML-формы в БД MySQL. PHP не предлагать,
можно другие Java-технологии.
    

Ответы

Ответ 1

Быстрая реализация с использование сервлета:

html


    
        
        
    
     
        
    
            
            
            
        
    



TheFirstServlet

public class FirstServlet extends HttpServlet {

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    }

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    processRequest(request, response);
}

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    request.setCharacterEncoding("UTF-8");  
    String login = request.getParameter("login");
    String password = request.getParameter("password");  
    insert(login, password);
    PrintWriter printWriter;
    try {
        printWriter = response.getWriter(); 
        printWriter.println("Ну, попытались");
    } catch(IOException exc) {}
}

public void insert(String login, String password) {
    Connection conn = null;
    Statement stmt = null;
    try {
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection("jdbc.url", "jdbc.login", "jdbc.password");
    stmt = conn.createStatement();
    String sql = "INSERT INTO NAME_TABLE (`login`,`password`)" +
            "VALUES ('" + login + "','" + password + "')";
    stmt.executeUpdate(sql);
    } catch (SQLException | ClassNotFoundException exc) {
        exc.printStackTrace();
        System.out.println("Не записал");
    }
    finally {
        try {
            if(conn!=null) conn.close();
            if(stmt!=null) stmt.close();
        } catch (SQLException exc) {}
    }
  }


Ну, как-то так по-простому.


Ответ 2

Вот первый попавшийся пример который подходит под вашу задачу:

/STEP 1. Import required packages
import java.sql.*;

public class JDBCExample {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";

   //  Database credentials
   static final String USER = "username";
   static final String PASS = "password";

   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   try{
      //STEP 2: Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      //STEP 3: Open a connection
      System.out.println("Connecting to a selected database...");
      conn = DriverManager.getConnection(DB_URL, USER, PASS);
      System.out.println("Connected database successfully...");

      //STEP 4: Execute a query
      System.out.println("Inserting records into the table...");
      stmt = conn.createStatement();

      String sql = "INSERT INTO Registration " +
                   "VALUES (100, 'Zara', 'Ali', 18)";
      stmt.executeUpdate(sql);
      sql = "INSERT INTO Registration " +
                   "VALUES (101, 'Mahnaz', 'Fatma', 25)";
      stmt.executeUpdate(sql);
      sql = "INSERT INTO Registration " +
                   "VALUES (102, 'Zaid', 'Khan', 30)";
      stmt.executeUpdate(sql);
      sql = "INSERT INTO Registration " +
                   "VALUES(103, 'Sumit', 'Mittal', 28)";
      stmt.executeUpdate(sql);
      System.out.println("Inserted records into the table...");

   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(stmt!=null)
            conn.close();
      }catch(SQLException se){
      }// do nothing
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
   }//end try
   System.out.println("Goodbye!");
}//end main
}//end JDBCExample

Медленная работа с БД через PreparedStatement

#java #mysql #jdbc #dao #preparedstatement

Как мне в этом коде, сделать так, чтобы я верно использовал PrepareStatement? 
Я уже всю реализацию ДАО не скидываю, так как очень много кода. В моем коде PrepareStatement
замедляет работу, а нужно чтобы ускорял. Я его использую не верно. Я так понимаю, мне
его как то открыть надо один раз, потом использовать везде как-то, и потом закрыть?
Буду рад любой помощи. 

public class MySqlDaoStudent implements DaoStudent {
    private PreparedStatement stm=null;

    private final Connection connection;

    private ResultSet rs=null;

    public MySqlDaoStudent(Connection connection) {
        this.connection = connection;
    }

    @Override
    public Student read(int key) throws SQLException {
        String sql="SELECT * FROM student_db.student where id = ?;";
        Student student = new Student();
        try {
            stm = connection.prepareStatement(sql);
            stm.setInt(1, key);
            rs = stm.executeQuery();
            rs.next();
            student.setId(rs.getInt("ID"));
            student.setFirst_name(rs.getString("FIRST_NAME"));
            student.setSecond_name(rs.getString("SECOND_NAME"));
         } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(rs!=null){
                rs.close();
            } 
            if(connection!=null){
                connection.close();
            }
            if (stm!=null){
                stm.close();
            }
        }
        return student;
    }

    @Override
    public List getAll() throws SQLException {
        String sql="SELECT * FROM student_db.student";
        List list=new ArrayList<>();
        Student student=new Student();
        try{
            stm=connection.prepareStatement(sql);
            rs=stm.executeQuery();
            while(rs.next()){
                student.setId(rs.getInt("ID"));
                student.setFirst_name(rs.getString("FIRST_NAME"));
                student.setSecond_name(rs.getString("SECOND_NAME"));
                list.add(student);
            }
        } catch (SQLException e){
            e.printStackTrace();
        } finally {
            if(rs!=null){
                rs.close();
            }
            if(connection!=null){
                connection.close();
            }
            if (stm!=null){
                stm.close();
            }
        }
        return list;
    }
}

    

Ответы

Ответ 1

Смысл PreparedStatement в том, чтобы скомпилировать запрос один раз и использовать
все время меняя только параметры запроса. Фактически, компиляция запроса происходит
при вызове PrepareStatement() - ваша проблема в том, что вы при каждом выполнении запроса
заново компилируете запрос. 

Отделите мух от котлет, а именно: разделите этап компиляции запроса и этап выполнения
прекомпилированного запроса.

Ну и на закуску: поскольку PreparedStatement привязан к Connection, то при закрытии
соединения естественно вы теряете свой прекомплированный запрос (на что вам и указывают
в комментариях)


Ответ 2

рекомендую модернизировать код таким образом 

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

/**
 * Created by TS on 07.02.2017.
 */
public class MySqlDaoStudent implements DaoStudent {
    private static final String ALL_STUDENTS = "SELECT * FROM student_db.student";
    private PreparedStatement stm = null;
    private PreparedStatement allPreparedStatement = null;

    private final Connection connection;

    public MySqlDaoStudent(Connection connection) {
        this.connection = connection;
    }

    private void init() throws SQLException {
        if (allPreparedStatement == null)
            allPreparedStatement = connection.prepareStatement(ALL_STUDENTS);

    }

    public Student read(int key) throws SQLException {
        // у нас каждый раз новый key, и так как у нас 1 параметр, запрос легче сформировать так
        String sql = String.format("SELECT * FROM student_db.student where id = %s;",
key);
        Student student = new Student();
        try (ResultSet rs = stm.executeQuery(sql)) { // такая конструкция позволяет
закрывать rs (вместо finally) 
            rs.next();
            student.setId(rs.getInt("ID"));
            student.setFirst_name(rs.getString("FIRST_NAME"));
            student.setSecond_name(rs.getString("SECOND_NAME"));
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return student;
    }

    public List getAll() throws SQLException {
        init();
        List list = new ArrayList<>();
        Student student = new Student();
        try (ResultSet rs = allPreparedStatement.executeQuery()) {
            while (rs.next()) {
                student.setId(rs.getInt("ID"));
                student.setFirst_name(rs.getString("FIRST_NAME"));
                student.setSecond_name(rs.getString("SECOND_NAME"));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
        return list;
    }

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


Ответ 3

Верное использование PreparedStatement заключается в том, что необходимо подготовить
запрос в СУБД, прежде, чем он будет отправлен. Его нужно подготовить, сохранить в поле,
а потом использовать, таким образом достигается максимальная скорость обработки любых
запросов в любом кол-ве. Вот код решения моего вопроса: 
(sql запрос сохранил в константу)

  /*   Константа sql запроса */
  private static final String SQL_SELECT_ALL_STUDENT = 
  "SELECT id, first_name, second_name FROM student_db.student";

  /* поле подключения */
  private Connection connection = null;
  /* поле PS */
  private PreparedStatement psSelectAllStudent = null;

 // использую Properties для чтения user,password,driver,url
 // подключаюсь к СУБД
  private Connection getConnection() throws DaoException {
  Properties property = new Properties();
  InputStream inputStream = null;
    try {
       inputStream = this.getClass().getResourceAsStream("/config.properties");
        property.load(inputStream);
        String url = property.getProperty("url");
        String user = property.getProperty("user");
        String password = property.getProperty("password");
        String driver = property.getProperty("driver");
        Class.forName(driver);
        if (connection == null) {
            connection = DriverManager.getConnection(url, user, password);
        }
    } catch (IOException ex) {
        DaoException er = new DaoException("ошибка загрузки файла ", ex);
        throw er;
    } catch (ClassNotFoundException ex) {
        DaoException er = new DaoException("ошибка Class.forName(driver) ", ex);
        throw er;
    } catch (SQLException ex) {
        DaoException er = new DaoException("ошибка соединения с СУБД ", ex);
        throw er;
    }
    return connection;
}


  //подготавливаю PS в СУБД и сохраняю в поле

    private void selectAllStudentPs(String sql) throws DaoException {
    getConnection();
    try {
        if (psSelectAllStudent == null) {
            psSelectAllStudent = connection.prepareStatement(sql);
        }
    } catch (SQLException er) {
        DaoException ex = new DaoException("ошибка подготовки запроса в selectStudentPs
", er);
        throw ex;
    }
}

  //получаю список того, что было необходимо сделать в задании 
   @Override
    public List selectAllStudent() throws DaoException {
    ResultSet resultSet=null;
    List list = new ArrayList<>();
    try {
        selectAllStudentPs(SQL_SELECT_ALL_STUDENT);
        resultSet = psSelectAllStudent.executeQuery();
        while (resultSet.next()) {
            Student ss = new Student();
            ss.setId(resultSet.getInt("id"));
            ss.setFirstName(resultSet.getString("first_name"));
            ss.setSecondName(resultSet.getString("second_name"));
            list.add(ss);
        }
    } catch (SQLException ex) {
        DaoException er = new DaoException("ошибка выполения SQL_SELECT_ALL_STUDENT
", ex);
        throw er;
    }finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException ex) {
                DaoException er = new DaoException("ошибка закрытия resultSet в " +
                        "selectStudent ", ex);
                throw er;
            }
        }
    }
    return list;
}
    /* ---- */


Таким образов и получается именно ускорение PreparedStatement !

Не могу установить соединение с базой данных MySQL с JDBC

#java #mysql #jdbc #intellij

JDBC.java



    import java.sql.*;

    public class JDBC {
        public static void main(String[] args) {
            String url="jdbc:mysql://localhost:3306/currency";
            String username = "root";
            String password = "root";
            try {
                Class.forName("com.mysql.jdbc.Driver");
                Connection connection = DriverManager.getConnection(url, username,
password);
                Statement statement = connection.createStatement();
                ResultSet resultSet = statement.executeQuery("SELECT * FROM rates");
                while (resultSet.next()) {
                    System.out.println(resultSet.getString(0));
                }
                resultSet.close();
                statement.close();
                connection.close();
            } catch (SQLException | ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
    }



В pom.xml зависимость для JDBC установлена.

Вывод:



    com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not
create connection to database server.
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:488)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
        at com.mysql.jdbc.Util.getInstance(Util.java:360)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2311)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
        at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:790)
        at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44)
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:488)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:395)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
        at by.bsu.pt.back.JDBC.main(JDBC.java:13)
    Caused by: java.lang.NullPointerException
        at com.mysql.jdbc.ConnectionImpl.getServerCharset(ConnectionImpl.java:2983)
        at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1873)
        at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1802)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1206)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2234)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
        ... 13 more



Ошибка в строке:



    Connection connection = DriverManager.getConnection(url, username, password);



Но при этом:

    

Ответы

Ответ 1

Проблема была решена сменой версии драйвера в pom.xml на 8.0.11 и добавлением параметра
useSSL=false в строку адреса

String url="jdbc:mysql://localhost:3306/currency?useSSL=false";

Дайте грамотный совет. Занимаюсь разработкой своего ORM

#jdbc #java #orm

Всем привет. 
Занимаюсь по-тихоньку развитием своей библиотеки объектно-реляционного отображения
для Java на основе JDBC. Сразу говорю: я не профессиональный программист, я всего лишь
студент 4 курса, но интерес к данной теме есть и хочется сделать что-то стоящее (разбираюсь
в новых темах по мере необходимости). Есть желание сделать библиотеку такой, чтобы
она была интересна не только мне. 
В данный момент уже реализовано:

Представление записей в виде объектов сущностных классов, один объект = одна запись.
Один сущн. класс = одна таблица;
Транзакции;
Автоматическая обработка исключений внутри класса EntityClass (обработка вынесена
в отдельный скрытый метод, там происходит вызов rollback() или метода для переподключения,
в зависимости от ситуации, а так же прочие действия);
Автоматическое переподключение в случае потери соединения (выполняется только если
не используется транзакция);
Подгрузка настроек из properties-файла;
Подгрузка подготовленных запросов из файла (будет ещё дорабатываться, хочу что-то
вроде хранимых процедур, но на стороне клиента, желательно даже с параметрами);
Набор методов в классе Functions для автоматической выборки и прочих вещей (будет
ещё дорабатываться);
Что-то вроде кеширования, но я не знаю, кеширование ли это. Помимо того, что можно
значения полей получать напрямую из ResultSet'а, есть возможность вызвать метод initialization()
и тогда содержимое ResultSet'а перегонится в Map, хранимый в объекте EntityClass. После
этого нужно вызывать отдельный метод для получения значений из Map'а, отображение не
зависят от состояния подключения к БД.
Несколько методов для связывания объектов. Например, метод insertObject(fieldName
: String, object : EntityClass) : void вставляет в указанное поле объект сущн. класса
(а на самом деле просто записывает в это поле внешний ключ). Ещё метод returnObject(fieldName
: String, tableName : String) : EntityClass извлекает из указанного поля объект сущн.
класса, принадлежащий указанной таблице. Методы для прочих связей в РБД пока не реализованы.

В принципе, это всё, что пока есть. Хочу обратить внимание на то, что в данный момент
есть два способа работы с сущн. классами: создание экземпляра класса EntityClass с
передачей ему в качестве параметра имени таблицы, с которой он связан и наследование
от EntityClass. При наследовании нужно класс-предок оформлять по строгому шаблону. Такому:
package entity;
import java.sql.SQLException;
import java.util.ArrayList;
import db_and_orm.EntityClass;
import db_and_orm.Functions;
import db_and_orm.Transaction;

public class Кафедра extends EntityClass {

    private final static String TABLE_NAME = "кафедры";

    //конструкторы для добавления новой записи 
    //после их вызова нужно вызывать "сеттеры", а затем insertRow)
    //полная выборка
    public Кафедра(Transaction trans) {
        super(trans, TABLE_NAME);
    }

    //частичная выборка
    public Кафедра(Transaction trans, String... поля) {
        super(trans, TABLE_NAME, поля);
    }

    //конструкторы для извлечения готовых записей
    //полная выборка
    public Кафедра(Transaction trans, int id) {
        super(trans, TABLE_NAME, id);
    }

    //частичная выборка
    public Кафедра(Transaction trans, int id, String... поля) {
        super(trans, TABLE_NAME, id, поля);
    }

Как видно, поля там не определены, потому что они определяются сами с помощью метаданных
(доступ к полям у меня осуществляется исключительно по имени, передаваемому методу
get().  То есть POJO классы не используются. Сейчас думаю, а может всё таки попытаться
адаптировать всё на POJO классы? Но тогда вопрос, если я буду наследоваться от EntityClass,
то откуда базовый класс будет знать, какие конкретно поля я определил в дочернем? Делать
абстрактный метод я не хочу, это не дело. 
Хочу позже ещё реализовать следующие вещи:


Хранимые процедуры и функции;


Логирование. Вот тут вопрос: что конкретно нужно логировать в плане работы с БД?
Может, дадите ссылку, где описывается, как происходит логирование по-нормальному?


Допиливание связей;


Применение аннотаций для упрощения работы с библиотекой;


Автоматическое составление структуры БД и отправка запросов на создание таблиц по
составленной объектной модели на уровне ЯП. Хочу без использования всяких XML, чтобы
составление схемы БД происходило только по сущн. классам;


Поддержка составных ключей;


Какой-нибудь контейнер, где будут выполняться операции над группой объектов сущн.
классов. Ну, в общем, хранение объектов и их обработка. Но тут вопрос: какие конкретно
операции могут производиться над группой объектов?


Рефлексивные отношения (вот это под вопросом, я честно говоря, не понимаю зачем они
вообще нужны). 


Вроде всё, до чего додумался. Ещё вопрос: как происходит реализация наследования,
если в РБД его нет. Я думаю, что если класс Б наследуется от А, то должна быть таблица,
которая содержит поля и класса А и класса Б. В классе А нужно как-то ограничивать кол-во
полей.
Цель этого поста: чтобы его прочитали и написали грамотный комментарий и ответы на
вопросы. 
Так же, ответьте на вопрос: что должна иметь ORM-библиотека, чтобы называться полноценной?
Я всё перечислил или что-то упустил? И что нужно, чтобы библиотека была интересна не
только мне?
Заранее спасибо :)    

Ответы

Ответ 1

Я не великий специалист в разработке ORM, как говорится:

чукча не писатель, чукча читатель


Уберите русскоязычные названия классов, я знаю, что javac и IDE умеют не ругаться
на не ASCII идентификаторы, но что более важно, на это ругаются 99.9% девелоперов.
Здесь дело не в неуважении к собственной стране, а в правилах. Почему то ведь вся медицина
на латыни работает и ничего... Здесь то же самое.
Логгирование. Недавно здесь обсуждались системы логгирования и народ сошелся, что
наиболее продвинутым будет использование библиотеки логгирования SLF4J - позволяющей
присоединять более-менее любую систему логгирования. Можно и старый добрый Log4J. Обычно
в логи ORM выдают (в зависимости от уровня логгирования) - SQL запросы которые лежат
в основе экзерсисов ORM.
Получение JDBC коннекта через JNDI имена/ресурсы (javax.sql.DataSource)
Кэширование JDBC коннектов, в частности реализация JDBC Pool
Кэширование SQL запросов (PreparedStatement/CallableStatement)
Кэширование результатов SQL запросов - ResultSet
Реализация разных моделей кэширования, например классический LRU (Least Recently
Used) и проч. - вагон и маленькая тележка

Ну и т.д.

Как blob.sql конвертировать на String

#java #jdbc #extjs

Есть вывод с базы JDBC, и там прописан код

                   str += ("[" +
                    "'" + rs.getInt("ID") + "', " +
                    "'" + rs.getString("NAME") + "',  " +
                    "'" + rs.getString("TYPE") + "',  " +
                    "'" + rs.getString("DATA") + "', " +
                    "'" + rs.getBlob("MESSAGE") + "', " +
                    "'" + rs.getInt("ID2") + "', " +
                    "'" + rs.getString("NAMEDOCUMENT") + "', " +
                    "'" + rs.getInt("ID1") + "', " +
                    "'" + rs.getString("STATUS") +
                    "'],");


поля Message у меня типа blob, и когда я принимаю его в Ext JS то value у него ссылка
на объект как конвертировать что бы на Ext JS выходило значения ??
    

Ответы

Ответ 1

BLOB (binary large object) — это двоичные данные неспецифицированного формата. Не
очень понятно, почему у вас Message (вероятно, текстовые данные) хранятся в виде BLOB.
Для больших текстовых данных должен использоваться тип CLOB (character large object).

Если вы хотите вывести содержимое BLOB, интерпретируя его, как текст, вам надо знать,
в какой кодировке этот текст был туда записан. Предположим, UTF-8. Тогда можно сделать так:

// ограничим максимальную длину блока данных, чтобы память не кончилась
int LENGTH_LIMIT = 100_000; 
Blob blob = rs.getBlob("MESSAGE");
long length = blob.length();
// а лучше кидать исключение, если неожиданно слишком большая длина
if(length > LENGTH_LIMIT) length = LENGTH_LIMIT;
byte[] data = blob.getBytes(0, (int)length);
String message = new String(data, StandardCharsets.UTF_8);
// теперь используйте message


В заключение скажу, что формировать JSON конкатенацией строк очень плохо. Вы сильно
попадёте на первой одинарной кавычке внутри текста сообщения. Есть много готовых библиотек
для того, чтобы сделать это быстро и безопасно.

Как правильно вывести таблицу из MySQL на JSP?

#jdbc #java #mysql

Здравствуйте, подскажите, как ПРАВИЛЬНО сделать вывод таблицы из БД на JSP-страницу
на основе JDBC (без внесения логики на JSP)?     

Ответы

Ответ 1

Правильно это:

Получить данные в сервлете/контроллере/DAO, в общем за пределами JSP. Средствами
JDBC: создаем Connection, создаем Statement, запускаем Statement, забираем ResultSet.
Преобразуем данные в более/менее адекватное представление, например в список экземпляров
класса, который эти данные оборачивает (т.н. Value Object, Data Transfer Object).
Передаем этот список в  JSP.
В JSP пробегаемся по списку и формируем табличку.

.

  

  

    
${person.name}
    
${person.age}
    
${person.height}
  
  

Обязательно ли закрывать Statement и ResultSet?

#servlet #jdbc #java

Java сервлет при каждом вызове получает Connection с SQL базой данных (Oracle oracle.jdbc.driver.OracleDriver,
используя DriverManager.getConnection()), затем создает Statement и ResultSet. Все
в локальных переменных. Очевидно (везде пишут об этом), что Statement и ResultSet надо
закрывать.
Вопрос: будет ли утечка ресурсов, если не закрыть ResultSet или Statement?
UPD (в ответ на ответы).
Итак, Oracle XE development, соответственно, версия бесплатная, ограниченная (но
настолько!)
Прежде всего, Statement и ResultSet в нормальном режиме я закрывал, а вот для Exception(s)
ограничился только закрытием Connection в try { ... } finally { ... }
Поэтому для тестирования сначала выбросил Statement и ResultSet close() и в непрерывном
цикле на 77 connection все свалилось, но свалилось по причине ORA-12519. Не Могу ОТКРЫТЬ
новую connection!!! Вот те раз, я же их закрываю! Вернул все close() - то же самое.
Я ведь в непрерывном цикле запросы не гонял, предположить такую подлянку не мог!
Пробую цикл с одним соединением (при закрытии Statement и ResultSet) все работает
(1000000 циклов). Если не закрывать, сначала падает по ResultSet, потом по Statement. 
Почитал про ORA-12519 в сети, увеличил ресурсы (alter system set processes=150 scope=spfile;,
это кому-то помогло) падение отодвинулось до 230 соединений и все.
Потом вычитал, оказалось, дело в том, что ограничено количество соединений в единицу
времени. Сделал 100 в секунду - то идет, то валится. Сейчас идет с 50 в секунду (цикл
100000 после каждой сотни открытых-закрытых sleep(2000)) - YES - прошло. 
Так что, спасибо всем!!!
UPD-2
Искал оптимальную задержку (при повторе попытки открыть новое соединение) оказалось
350 миллисекунд - для 1000 циклов у меня общая задержка в худшем случае 6.3 с. Почему-то
она плавает, нет повторяемости. Для других задержек (от 50мс до 3с) общее время хуже.
Нет ли у кого идей, как правильно организовать подобные замеры?
UPD-3
Oracle - это загадка. Попробовал на работе с "боевой" 11.g Enterprise Edition на сервере.
Выполняется раз в 10 медленнее, поэтому гонял до 10000 циклов. Нет сбоев! Без разницы,
закрываю Statement и ResultSet или нет. Работает и все тут (без всяких sleep()), хотя
драйвер в программе тот же самый от XE Development.
У кого-нибудь есть идеи???  Как вообще правильно программировать взаимодействие с
базой?    

Ответы

Ответ 1

Насчет statement не уверен, но точно знаю, что с resultset будет. Кажется, предел
находится на 32к незакрытых resultset, oracle не сможет возвращать результаты выборки.
имхо, лучше всегда за собой убирать :)


Ответ 2

Спецификация JDBC рекомендует всегда все закрывать вручную. Однако при этом утверждается, что 

all Statement objects will be closed when the connection that created them is closed

и что 

closing a Statement object will close and invalidate any instances of ResultSet produced
by that Statement object

т.е. закрытие соединения должно закрыть все порожденные объекты иерархически. Однако
авторы, видимо, решили перестраховаться и защитить вас от случаев, когда "должно" в
реализации драйвера перетекает в "мы постараемся" - по техническим причинам либо из-за
невнимательности. Видимо, специкация спецификацией, а на практике "всякое может быть"
(как бы быдлокодно это не звучало) - так что лучше закрывать самостоятельно. 
Лично ине кажется, что в драйвере Оракла тут все же будет ОК и с автоматическим закрытием,
а вот при переходе на другую БД могут быть сюрпризы.

Как в java сделать comit в sql если я хочу закомитить много объектов?

#java #sql #запрос #jdbc

Делаю так:

PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO mydbcall1.Call0 (date,direction,operator,abonentTel,duration,coast1,coast2,corpPhone)" +
                " VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
try {

    ps.setDate(1,  new java.sql.Date(call.date.getTime()) );
    ps.setString(2, call.direction);
    ps.setString(3, call.operator);
    ps.setString(4, call.abonentTel);
    ps.setInt(5, call.duration);
    ps.setDouble(6, call.coast1);
    ps.setDouble(7, call.coast2);
    ps.setString(8, call.corpPhone);
    ps.executeUpdate(); // for INSERT, UPDATE & DELETE

} finally {
    ps.close();
}


метод для 3000 объектов работает больше 1.5 минут.
    

Ответы

Ответ 1

Не создавайте PreparedStatement каждый раз заново. Преимущество PreparedStatement
перед обычным Statement именно в том, что можно его заранее отправить на сервер БД
для компиляции и переиспользовать повторно.
Отправляйте вставки пакетами (batch). Вероятно, вам придется подобрать оптимальный
размер пакета.
Используйте явное управление транзакцией. По-умолчанию транзакция завершается на
каждый запрос к БД (autoCommit = true).


Вот пример с применением этих трех рекомендаций (используется try-with-resources):

private static final String INSERT_STATEMENT = "INSERT INTO mydbcall1.Call0(date,direction,operator,abonentTel,duration,coast1,coast2,corpPhone)
VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

// ....

try (
    Connection connection = database.getConnection();
    PreparedStatement ps = connection.prepareStatement(INSERT_STATEMENT);
) {
    int i = 0;
    connection.setAutoCommit(false)
    for (Call call : calls) {
        ps.setDate(1, new java.sql.Date(call.date.getTime()) );
        ps.setString(2, call.direction);
        ps.setString(3, call.operator);
        ps.setString(4, call.abonentTel);
        ps.setInt(5, call.duration);
        ps.setDouble(6, call.coast1);
        ps.setDouble(7, call.coast2);
        ps.setString(8, call.corpPhone);
        // ...

        ps.addBatch();
        i++;

        if (i % 1000 == 0 || i == calls.size()) {
            ps.executeBatch(); // ограничиваем размер одного пакета тысячей вставок
        }
    }
    connection.commit();
} catch(SQLException e) { 
    connection.rollback();
}

Защита от SQL инъекций в jdbc java

#java #безопасность #jdbc #preparedstatement #sql_injection

Часто вижу утверждения, что надо использовать PreparedStatement вместо обычного Statement,
чтобы защититься от sql инъекций. Как он защищает?
    

Ответы

Ответ 1

Коротко, для нетерпеливых:

При использовании Statement строки запроса и значений складываются.

При использовании PreparedStatement имеется шаблон запроса и данные в него вставляются,
с отражением кавычек.

Ниже подробнее с примерами.



Вступление.

Имеем такую простую таблицу с данными.

+-----------+----+--------+
| userName  | id |  pass  |
+-----------+----+--------+
| admin     |  1 |  admin |
| user      |  2 |  pass  |
| chuchelo  |  3 |  elli  |
+-----------+----+--------+


Модель User, будет содержать имя и пароль, а так же метод логин, который спросит
данные с консоли.

class UserLogin {
    String name;
    String pass;

    public UserLogin() {
    }

    public void login()  {
        BufferedReader reader = null;
        try{
            reader = new BufferedReader(new InputStreamReader(System.in));

            System.out.println("user name: ");
            name = reader.readLine();

            System.out.println("pass: ");
            pass = reader.readLine();
       } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (reader != null)
                try {
                    reader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
        }
    }




Метод, который будет работать с обычным Statement:

UserLogin user = new UserLogin();
user.login();
try (Connection connect = MyConnection.getConnection()){
    Statement statement = connect.createStatement();
    String query = "SELECT userName, id, pass FROM users WHERE userName='" + user.name
+ "' AND pass = '" + user.pass + "'";
    System.out.println(query);
    ResultSet resultSet = statement.executeQuery(query);

    while (resultSet.next()){
        System.out.printf("User: id=%d name=%s pass=%s\n",
                resultSet.getInt("id"),
                resultSet.getString("userName"),
                resultSet.getString("pass"));
    }
    MyConnection.closeConnect();
} catch (SQLException e) {
    e.printStackTrace();
}


Теперь если мы запустим этот метод и введем в консоль данные без инъекции:

user name: admin
pass: admin

User: id=1 name=admin pass=admin


При этом сам запрос выглядит так:

 SELECT userName, id, pass FROM users WHERE userName='admin' AND pass = 'admin'


Если допустить ошибку в имени или пароле, то данные выведены не будет. 

Теперь попробуем использовать инъекцию(' or'1'='1), т.е. введем такие данные:

user name: admin' or'1'='1
pass: blabla


То мы все равно получаем результат, несмотря на то, что пароль неверный:

 User: id=1 name=admin pass=admin


При этом сам запрос теперь выглядит так:

  SELECT userName, id, pass FROM users WHERE userName='admin' or'1'='1' AND pass
= 'blabla'


т.к. выражение or'1'='1' всегда равно true, то даже без указания пароля мы получим
все данные.



Как от этого защитит PreparedStatement?

Метод который будет получать данные из базы с помощью PreparedStatement:

UserLogin user = new UserLogin();
user.login();
try (Connection connect = MyConnection.getConnection()){
    String query = "SELECT userName, id, pass FROM users WHERE userName=? AND pass=?";
    PreparedStatement statement = connect.prepareStatement(query);
    statement.setString(1, user.name);
    statement.setString(2, user.pass);
    System.out.println(statement);
    ResultSet resultSet = statement.executeQuery();

    while (resultSet.next()){
        System.out.printf("User: id=%d name=%s pass=%s\n",
        resultSet.getInt("id"),
        resultSet.getString("userName"),
        resultSet.getString("pass"));
    }
    MyConnection.closeConnect();
} catch (SQLException e) {
    e.printStackTrace();
}


Все тоже самое, только заменили обычный Statement на PreparedStatement. Надеюсь вы
на слово поверите, что при правильных данных мы получим верный результат, если нет
то вот лог в консоли:

user name: user
pass: pass
User: id=2 name=user pass=pass
Запрос:
SELECT userName, id, pass FROM users WHERE userName='user' AND pass='pass'


А теперь попробуем использовать инъекцию:

user name: user' or'1'='1
pass: inject


И ответа не получаем, потому что запрос выглядит так:

  SELECT userName, id, pass FROM users WHERE userName='user\' or\'1\'=\'1' AND pass='inject'


Т.е. все кавычки были отражены слешем, инъекция не удалась. 



Отличие Statement от PreparedStatement:

Statement - вы должны заботиться о кавычках в запросе и ставить их там где они нужны.

PreparedStatement - вставляет значения в запрос и за счет методов setString setInt
и прочих. Он сам понимает где нужны кавычки, а где нет. Соответственно все входные
данных оборачивает ими.

Как задать кодировку при подключении к бд MySQL?

#java #mysql #jdbc

Добрый день, подключаюсь к БД таким образом:

private static final String url = "jdbc:mysql://172.0.0.1/klon";

private static final String user = "root";

private static final String password = "root";


Возник вопрос как можно задать кодировку UTF-8 сразу при подключении то есть сказать
MySQL что данные будут поступать на русском?
    

Ответы

Ответ 1

Кодировка задается в URL: jdbc:mysql://172.0.0.1/klon?characterEncoding=utf8

Подстановка параметра PreparedStatement в JDBC Oracle

#java #oracle #jdbc

Столкулся с тем, что данная конструкция подстановки параметра (:1) работает с драйвером
Oracle:

    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        try (Connection con =
                 DriverManager.getConnection("HOST", "USER", "PASS")) {
            try (PreparedStatement preparedStatement =
                     con.prepareStatement("select * from TABLE where name = :1")) {
                preparedStatement.setString(1, "NAME");
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    while (resultSet.next()) {
                        System.out.println(resultSet.getObject(1));
                    }
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }


В JSR указано использование только знака ?.
Это особенность реализации драйвера?  

UPD:  

Версия БД:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production

Что замечено еще:  

    try (PreparedStatement preparedStatement =
             con.prepareStatement("select * from TABLE where name = :abc and id =
:dcf")) {
        preparedStatement.setString(1, "PARAM");
        preparedStatement.setInt(2, 173);
        try (ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                System.out.println(resultSet.getObject(1));
            }
        }
    }


просьба обратить внимание на название подстановок, этот код работает.
А вот этот уже не работает:  

    try (PreparedStatement preparedStatement =
             con.prepareStatement("select * from TABLE  where name = :abc and id
= :dcf")) {
        preparedStatement.setString(2, "NAME");
        preparedStatement.setInt(1, 173);
        try (ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                System.out.println(resultSet.getObject(1));
            }
        }
    }

    java.sql.SQLSyntaxErrorException: ORA-01722: неверное число


То есть на название вообще нет реакции, только на ":" порядок.
    

Ответы

Ответ 1

В JSR указано использование только знака ?.


Символ ? это ещё не подстановочный параметр, это только местозаполнитель (placeholder),
все символы местозаполнители функция prepareStatement() 
заменит на подстановочные переменные (или переменные связывания (bind variables))
используя синтаксис для подстановочных переменных целевой СУБД. 

Если в SQL выражении указать подстановочные параметры сразу в том виде, в котором
на целевой СУБД выполнится подготовка и компиляция (parse), то функция prepareStatement()
не призведёт никакой замены и не заметит, что её "лишили работы".


  Это особенность реализации драйвера?


Да, или лучше сказать - это особенность целевой СУБД, которую этот драйвер поддерживает. 
Касательно подстановочных переменных, различные СУБД имеют различный синтаксис этих
переменных:


Oracle: :var (colon) 
MySQL, DB2 and Firebird: ? (question marks) 
Sybase, MS SQL Server: @var (at sign) 
PostgreSQL: $var (dollar sign)


Для подтверждения вышесказанного, рассмотрим следующий пример.

Выполним один и тот же запрос несколько раз с различным использованием местозаполнителей
и подстановочных параметров:

create or replace and compile java source named TestPrepared as
import java.sql.*;
public class TestPrepared {
    public static void selectValue (int id, String item) {
        String[] stmts = {
            /*#1*/"/* ? */ select id, item from items where id = ? and item = ?",
            /*#2*/"/* ? */ select id, item from items where id = :1 and item = :2",
            /*#3*/"/* ? */ select id, item from items where id = :2 and item = :3",
            /*#4*/"/* ? */ select id, item from items where id = :id and item = :item"};
        try {
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@dbsrv:1521/pdb1","sh","sh");
            for (int i=0; iНаписать об этом в блоге

inconsistent java and sql object types

Есть типы в БД:
CREATE OR REPLACE TYPE my_type IS OBJECT ( id VARCHAR2(20), name VARCHAR2(40), phone NUMBER );
Пытаюсь создать обьекты с использованием этих типов:
.... Object[] myArray = new Object[3]; Object[] struct = new Object[values.size()];
int arrayIndex = 0; for (User user : values) { myArray[0] = user.id().toString(); myArray[1] = user.getName().toString(); myArray[2] = user.getPhone();
struct[arrayIndex++] = con.createStruct("my_type",myArray); }
На строке с созданием структуры con.createStruct выпадает ошибка:
java.sql.SQLException: Inconsistent java and sql object types, for classes implementing ORAData or OracleData, respective factory classes ORADataFactory and OracleDataFactory should be registered in typeMap.
В чем может быть дело?

Ответ

Это помогло мне.
for (User user : values) { struct[arrayIndex++] = con.createStruct( "my_type", new Object[]{ user.id().toString(); user.getName().toString(); user.getPhone(); }); }

Вывод данных из базы в динамическую таблицу

Имеется вот такая примерно таблица -
#{msgs.customerIdHeader} #{customer.id} #{msgs.NDOKHeader} #{customer.NDOK} #{msgs.SODRABHeader} #{customer.SODRAB}
Как написать правильный код с использованием jdbc, который бы подключался к базе и получал данные, а затем они попадали в данную таблицу?

Ответ

1) Для подключения к MSSQL вам поднадобится соответствующий драйвер. В Maven Central его нету, поэтому вам будет нужно самостоятельно добыть внутри каталога установки драйвера:
\sqljdbc_\\sqljdbc.jar \sqljdbc_\\sqljdbc4.jar \sqljdbc_\\sqljdbc41.jar \sqljdbc_\\sqljdbc42.jar
согласно документации Microsoft
Скачать драйвер можно с сайта Microsoft
(Поясню на всякий случай: installation directory не надо никуда прописывать - это просто путь до драйвера при обычной установке из инсталлятора. Если у вас уже все скачано, достаточно добавить драйвер в CLASSPATH.)
Далее этот драйвер нужно добавить в CLASSPATH любым методом, специфичным для вашего проекта. Например, если вы собираете вручную из консоли, то это будет нечто вроде:
CLASSPATH =.;C:\Program Files\Microsoft JDBC Driver 6.0 for SQL Server\sqljdbc_4.2\enu\sqljdbc42.jar
CLASSPATH =.:/home/usr1/mssqlserverjdbc/Driver/sqljdbc_4.2/enu/sqljdbc42.jar
Если вы используете IntelliJ IDEA, то это делается так:
нужно зайти в настройки проекта (File->Project Structure) в разделе Modules найти ваш модуль на вкладке Dependencies в самом низу нажать на плюсик выбрать "JARs or Directories" добавить нужную директорию
Если используется Maven, то есть проблема, в Maven Central нету этого артефакта, то есть его придется оформить самостоятельно.
Для серьезных больших проектов, самый правильный способ - поднять собственный репозиторий. На выбор, например, есть Artifactory и Nexus. Как работать с этими инструментами - описывать слишком долго, не влезет в максимальный размер ответа. Скорей всего, у вас не тот случай, но я был обязан предупредить.
Для небольших проектов, можно установить нужный jar в локальный репозиторий Maven. Про это на сайте Maven есть специальная документация. Вкратце, достаточно просто запустить в терминале строчку:
mvn install:install-file -Dfile=<путь до джарки с жрайвером без угловых скобочек> -DgroupId=com.microsoft.sqlserver \ -DartifactId=com.microsoft.sqlserver.jdbc.SQLServerDriver -Dversion=4.2 -Dpackaging=jar
и далее добавить в pom.xml новую зависимость вида:
com.microsoft.sqlserver com.microsoft.sqlserver.jdbc.SQLServerDriver 4.2
В общем, добавление новой джарки с сборку проекта очень зависит от характеристик вашего проекта, способов сборки, деплоймента, итп. Тут все каждый решает для себя.
2) Далее у нас есть допустим Entity класс:
public class Customer {
private Integer id; private String NDOK; private String SODRAB;
public Customer() { }
public Customer(Integer id, String NDOK, String SODRAB) { this.id = id; this.NDOK = NDOK; this.SODRAB = SODRAB; }
public Integer getId() { return id; }
public void setId(Integer id) { this.id = id; }
public String getNDOK() { return NDOK; }
public void setNDOK(String NDOK) { this.NDOK = NDOK; }
public String getSODRAB() { return SODRAB; }
public void setSODRAB(String SODRAB) { this.SODRAB = SODRAB; } }
3) Далее создаем managed bean:
import java.io.Serializable; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.faces.bean.ManagedBean; import javax.faces.bean.SessionScoped;
@ManagedBean @SessionScoped public class CustomerBean implements Serializable {
private static final long serialVersionUID = 6081417964063918994L;
public List getCustomers() throws ClassNotFoundException, SQLException {
Connection connect = null;
//Привел пример, как указать еще и в url, это необязательно String url = "jdbc:sqlserver://localhost:1433;" + "databaseName=CustomerDatabase;user=user;password=password";
String username = "user"; String password = "password";
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
connect = DriverManager.getConnection(url, username, password); // System.out.println("Connection established"+connect);
} catch (SQLException ex) { System.out.println("in exec"); System.out.println(ex.getMessage()); }
List customers = new ArrayList(); PreparedStatement pstmt = connect .prepareStatement("select id, NDOK, SODRAB from Customer"); ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Customer customer = new Customer(); customer.setId(rs.getInt("id")); customer.setNDOK(rs.getString("NDOK")); customer.setSODRAB(rs.getString("SODRAB"));
customers.add(customer);
}
// close resources rs.close(); pstmt.close(); connect.close();
return customers;
}
}
4) Передаете данные в таблицу способом, описанным в стартовом посте.
5) PROFIT

Значения по умолчанию при вставке с помощью PreparedStatement

Такой вопрос, есть дао класс с запросом insert последние три поля в базе данных postgresql имеют значение default,то есть как мне объяснили данные postgresql берет автоматически, какие есть способы сделать эти значения в Java?
Пример:
public void addSkisql(Skisql skisql) { try { PreparedStatement preparedStatement = connection .prepareStatement("insert into clippersql.skisql(nzak,osd_t,osd_c,osd_r,kiz,pri,nvp,naim,nizv,\"user\",datez,user_ip) values(?,?,?,?,?,?,?,?,?,?,?,?)"); // Parameters start with 1 preparedStatement.setString(1, skisql.getNzak()); preparedStatement.setString(2, skisql.getOsd_t()); preparedStatement.setString(3, skisql.getOsd_c()); preparedStatement.setString(4, skisql.getOsd_r()); preparedStatement.setString(5, skisql.getKiz()); preparedStatement.setInt(6, skisql.getPri()); preparedStatement.setString(7, skisql.getNvp()); preparedStatement.setString(8, skisql.getNaim()); preparedStatement.setString(9, skisql.getNizv()); preparedStatement.setString(10, skisql.getUser()); Date date = Date.valueOf(skisql.getDatez().toString()); preparedStatement.setDate(11, date); preparedStatement.setString(12, skisql.getUser_ip()); preparedStatement.executeUpdate();

Ответ

В данном случае, если задавать null, то при вставке записи будет использоваться дефолтное значение.
Integer obj = null; PreparedStatement preparedStatement; if (obj == null) { preparedStatement.setNull(1, Types.INTEGER); } else { preparedStatement.setInt(1, obj); }
Если СУБД не поддерживает эту возможность, то можно путем модифицирования запроса (составлять запрос с необходимым количеством параметров) либо добавлять хранимую процедуру.

Как занести данные из HTML-формы в MySQL при помощи JDBC?

Имеются 2 HTML-формы

и

База данных на MySQL Server, где присутствуют аналогичные поля login varchar (45) NOT NULL и password varchar (45) NOT NULL.
Подскажите, как при помощи JDBC записать данные HTML-формы в БД MySQL. PHP не предлагать, можно другие Java-технологии.

Ответ

Быстрая реализация с использование сервлета:
html

TheFirstServlet
public class FirstServlet extends HttpServlet {
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
@Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); }
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); request.setCharacterEncoding("UTF-8"); String login = request.getParameter("login"); String password = request.getParameter("password"); insert(login, password); PrintWriter printWriter; try { printWriter = response.getWriter(); printWriter.println("Ну, попытались"); } catch(IOException exc) {} }
public void insert(String login, String password) { Connection conn = null; Statement stmt = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc.url", "jdbc.login", "jdbc.password"); stmt = conn.createStatement(); String sql = "INSERT INTO NAME_TABLE (`login`,`password`)" + "VALUES ('" + login + "','" + password + "')"; stmt.executeUpdate(sql); } catch (SQLException | ClassNotFoundException exc) { exc.printStackTrace(); System.out.println("Не записал"); } finally { try { if(conn!=null) conn.close(); if(stmt!=null) stmt.close(); } catch (SQLException exc) {} } }
Ну, как-то так по-простому.

Медленная работа с БД через PreparedStatement

Как мне в этом коде, сделать так, чтобы я верно использовал PrepareStatement? Я уже всю реализацию ДАО не скидываю, так как очень много кода. В моем коде PrepareStatement замедляет работу, а нужно чтобы ускорял. Я его использую не верно. Я так понимаю, мне его как то открыть надо один раз, потом использовать везде как-то, и потом закрыть? Буду рад любой помощи.
public class MySqlDaoStudent implements DaoStudent { private PreparedStatement stm=null;
private final Connection connection;
private ResultSet rs=null;
public MySqlDaoStudent(Connection connection) { this.connection = connection; }
@Override public Student read(int key) throws SQLException { String sql="SELECT * FROM student_db.student where id = ?;"; Student student = new Student(); try { stm = connection.prepareStatement(sql); stm.setInt(1, key); rs = stm.executeQuery(); rs.next(); student.setId(rs.getInt("ID")); student.setFirst_name(rs.getString("FIRST_NAME")); student.setSecond_name(rs.getString("SECOND_NAME")); } catch (SQLException e) { e.printStackTrace(); } finally { if(rs!=null){ rs.close(); } if(connection!=null){ connection.close(); } if (stm!=null){ stm.close(); } } return student; }
@Override public List getAll() throws SQLException { String sql="SELECT * FROM student_db.student"; List list=new ArrayList<>(); Student student=new Student(); try{ stm=connection.prepareStatement(sql); rs=stm.executeQuery(); while(rs.next()){ student.setId(rs.getInt("ID")); student.setFirst_name(rs.getString("FIRST_NAME")); student.setSecond_name(rs.getString("SECOND_NAME")); list.add(student); } } catch (SQLException e){ e.printStackTrace(); } finally { if(rs!=null){ rs.close(); } if(connection!=null){ connection.close(); } if (stm!=null){ stm.close(); } } return list; } }

Ответ

Верное использование PreparedStatement заключается в том, что необходимо подготовить запрос в СУБД, прежде, чем он будет отправлен. Его нужно подготовить, сохранить в поле, а потом использовать, таким образом достигается максимальная скорость обработки любых запросов в любом кол-ве. Вот код решения моего вопроса: (sql запрос сохранил в константу)
/* Константа sql запроса */ private static final String SQL_SELECT_ALL_STUDENT = "SELECT id, first_name, second_name FROM student_db.student";
/* поле подключения */ private Connection connection = null; /* поле PS */ private PreparedStatement psSelectAllStudent = null;
// использую Properties для чтения user,password,driver,url // подключаюсь к СУБД private Connection getConnection() throws DaoException { Properties property = new Properties(); InputStream inputStream = null; try { inputStream = this.getClass().getResourceAsStream("/config.properties"); property.load(inputStream); String url = property.getProperty("url"); String user = property.getProperty("user"); String password = property.getProperty("password"); String driver = property.getProperty("driver"); Class.forName(driver); if (connection == null) { connection = DriverManager.getConnection(url, user, password); } } catch (IOException ex) { DaoException er = new DaoException("ошибка загрузки файла ", ex); throw er; } catch (ClassNotFoundException ex) { DaoException er = new DaoException("ошибка Class.forName(driver) ", ex); throw er; } catch (SQLException ex) { DaoException er = new DaoException("ошибка соединения с СУБД ", ex); throw er; } return connection; }
//подготавливаю PS в СУБД и сохраняю в поле
private void selectAllStudentPs(String sql) throws DaoException { getConnection(); try { if (psSelectAllStudent == null) { psSelectAllStudent = connection.prepareStatement(sql); } } catch (SQLException er) { DaoException ex = new DaoException("ошибка подготовки запроса в selectStudentPs ", er); throw ex; } }
//получаю список того, что было необходимо сделать в задании @Override public List selectAllStudent() throws DaoException { ResultSet resultSet=null; List list = new ArrayList<>(); try { selectAllStudentPs(SQL_SELECT_ALL_STUDENT); resultSet = psSelectAllStudent.executeQuery(); while (resultSet.next()) { Student ss = new Student(); ss.setId(resultSet.getInt("id")); ss.setFirstName(resultSet.getString("first_name")); ss.setSecondName(resultSet.getString("second_name")); list.add(ss); } } catch (SQLException ex) { DaoException er = new DaoException("ошибка выполения SQL_SELECT_ALL_STUDENT ", ex); throw er; }finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException ex) { DaoException er = new DaoException("ошибка закрытия resultSet в " + "selectStudent ", ex); throw er; } } } return list; } /* ---- */
Таким образов и получается именно ускорение PreparedStatement !

Защита от SQL инъекций в jdbc java

Часто вижу утверждения, что надо использовать PreparedStatement вместо обычного Statement, чтобы защититься от sql инъекций. Как он защищает?

Ответ

Коротко, для нетерпеливых:
При использовании Statement строки запроса и значений складываются.
При использовании PreparedStatement имеется шаблон запроса и данные в него вставляются, с отражением кавычек.
Ниже подробнее с примерами.

Вступление.
Имеем такую простую таблицу с данными.
+-----------+----+--------+ | userName | id | pass | +-----------+----+--------+ | admin | 1 | admin | | user | 2 | pass | | chuchelo | 3 | elli | +-----------+----+--------+
Модель User, будет содержать имя и пароль, а так же метод логин, который спросит данные с консоли.
class UserLogin { String name; String pass;
public UserLogin() { }
public void login() { BufferedReader reader = null; try{ reader = new BufferedReader(new InputStreamReader(System.in));
System.out.println("user name: "); name = reader.readLine();
System.out.println("pass: "); pass = reader.readLine(); } catch (IOException e) { e.printStackTrace(); }finally { if (reader != null) try { reader.close(); } catch (IOException e) { e.printStackTrace(); } } }

Метод, который будет работать с обычным Statement
UserLogin user = new UserLogin(); user.login(); try (Connection connect = MyConnection.getConnection()){ Statement statement = connect.createStatement(); String query = "SELECT userName, id, pass FROM users WHERE userName='" + user.name + "' AND pass = '" + user.pass + "'"; System.out.println(query); ResultSet resultSet = statement.executeQuery(query);
while (resultSet.next()){ System.out.printf("User: id=%d name=%s pass=%s
", resultSet.getInt("id"), resultSet.getString("userName"), resultSet.getString("pass")); } MyConnection.closeConnect(); } catch (SQLException e) { e.printStackTrace(); }
Теперь если мы запустим этот метод и введем в консоль данные без инъекции:
user name: admin pass: admin
User: id=1 name=admin pass=admin
При этом сам запрос выглядит так:
SELECT userName, id, pass FROM users WHERE userName='admin' AND pass = 'admin'
Если допустить ошибку в имени или пароле, то данные выведены не будет.
Теперь попробуем использовать инъекцию(' or'1'='1), т.е. введем такие данные:
user name: admin' or'1'='1 pass: blabla
То мы все равно получаем результат, несмотря на то, что пароль неверный:
User: id=1 name=admin pass=admin
При этом сам запрос теперь выглядит так:
SELECT userName, id, pass FROM users WHERE userName='admin' or'1'='1' AND pass = 'blabla'
т.к. выражение or'1'='1' всегда равно true, то даже без указания пароля мы получим все данные.

Как от этого защитит PreparedStatement?
Метод который будет получать данные из базы с помощью PreparedStatement
UserLogin user = new UserLogin(); user.login(); try (Connection connect = MyConnection.getConnection()){ String query = "SELECT userName, id, pass FROM users WHERE userName=? AND pass=?"; PreparedStatement statement = connect.prepareStatement(query); statement.setString(1, user.name); statement.setString(2, user.pass); System.out.println(statement); ResultSet resultSet = statement.executeQuery();
while (resultSet.next()){ System.out.printf("User: id=%d name=%s pass=%s
", resultSet.getInt("id"), resultSet.getString("userName"), resultSet.getString("pass")); } MyConnection.closeConnect(); } catch (SQLException e) { e.printStackTrace(); }
Все тоже самое, только заменили обычный Statement на PreparedStatement. Надеюсь вы на слово поверите, что при правильных данных мы получим верный результат, если нет то вот лог в консоли:
user name: user pass: pass User: id=2 name=user pass=pass Запрос: SELECT userName, id, pass FROM users WHERE userName='user' AND pass='pass'
А теперь попробуем использовать инъекцию:
user name: user' or'1'='1 pass: inject
И ответа не получаем, потому что запрос выглядит так:
SELECT userName, id, pass FROM users WHERE userName='user\' or\'1\'=\'1' AND pass='inject'
Т.е. все кавычки были отражены слешем, инъекция не удалась.

Отличие Statement от PreparedStatement
Statement - вы должны заботиться о кавычках в запросе и ставить их там где они нужны.
PreparedStatement - вставляет значения в запрос и за счет методов setString setInt и прочих. Он сам понимает где нужны кавычки, а где нет. Соответственно все входные данных оборачивает ими.

Как blob.sql конвертировать на String

Есть вывод с базы JDBC, и там прописан код
str += ("[" + "'" + rs.getInt("ID") + "', " + "'" + rs.getString("NAME") + "', " + "'" + rs.getString("TYPE") + "', " + "'" + rs.getString("DATA") + "', " + "'" + rs.getBlob("MESSAGE") + "', " + "'" + rs.getInt("ID2") + "', " + "'" + rs.getString("NAMEDOCUMENT") + "', " + "'" + rs.getInt("ID1") + "', " + "'" + rs.getString("STATUS") + "'],");
поля Message у меня типа blob, и когда я принимаю его в Ext JS то value у него ссылка на объект как конвертировать что бы на Ext JS выходило значения ??

Ответ

BLOB (binary large object) — это двоичные данные неспецифицированного формата. Не очень понятно, почему у вас Message (вероятно, текстовые данные) хранятся в виде BLOB. Для больших текстовых данных должен использоваться тип CLOB (character large object).
Если вы хотите вывести содержимое BLOB, интерпретируя его, как текст, вам надо знать, в какой кодировке этот текст был туда записан. Предположим, UTF-8. Тогда можно сделать так:
// ограничим максимальную длину блока данных, чтобы память не кончилась int LENGTH_LIMIT = 100_000; Blob blob = rs.getBlob("MESSAGE"); long length = blob.length(); // а лучше кидать исключение, если неожиданно слишком большая длина if(length > LENGTH_LIMIT) length = LENGTH_LIMIT; byte[] data = blob.getBytes(0, (int)length); String message = new String(data, StandardCharsets.UTF_8); // теперь используйте message
В заключение скажу, что формировать JSON конкатенацией строк очень плохо. Вы сильно попадёте на первой одинарной кавычке внутри текста сообщения. Есть много готовых библиотек для того, чтобы сделать это быстро и безопасно.

Обязательно ли закрывать Statement и ResultSet?

Java сервлет при каждом вызове получает Connection с SQL базой данных (Oracle oracle.jdbc.driver.OracleDriver, используя DriverManager.getConnection()), затем создает Statement и ResultSet. Все в локальных переменных. Очевидно (везде пишут об этом), что Statement и ResultSet надо закрывать. Вопрос: будет ли утечка ресурсов, если не закрыть ResultSet или Statement? UPD (в ответ на ответы). Итак, Oracle XE development, соответственно, версия бесплатная, ограниченная (но настолько!) Прежде всего, Statement и ResultSet в нормальном режиме я закрывал, а вот для Exception(s) ограничился только закрытием Connection в try { ... } finally { ... } Поэтому для тестирования сначала выбросил Statement и ResultSet close() и в непрерывном цикле на 77 connection все свалилось, но свалилось по причине ORA-12519. Не Могу ОТКРЫТЬ новую connection!!! Вот те раз, я же их закрываю! Вернул все close() - то же самое. Я ведь в непрерывном цикле запросы не гонял, предположить такую подлянку не мог! Пробую цикл с одним соединением (при закрытии Statement и ResultSet) все работает (1000000 циклов). Если не закрывать, сначала падает по ResultSet, потом по Statement. Почитал про ORA-12519 в сети, увеличил ресурсы (alter system set processes=150 scope=spfile;, это кому-то помогло) падение отодвинулось до 230 соединений и все. Потом вычитал, оказалось, дело в том, что ограничено количество соединений в единицу времени. Сделал 100 в секунду - то идет, то валится. Сейчас идет с 50 в секунду (цикл 100000 после каждой сотни открытых-закрытых sleep(2000)) - YES - прошло. Так что, спасибо всем!!! UPD-2 Искал оптимальную задержку (при повторе попытки открыть новое соединение) оказалось 350 миллисекунд - для 1000 циклов у меня общая задержка в худшем случае 6.3 с. Почему-то она плавает, нет повторяемости. Для других задержек (от 50мс до 3с) общее время хуже. Нет ли у кого идей, как правильно организовать подобные замеры? UPD-3 Oracle - это загадка. Попробовал на работе с "боевой" 11.g Enterprise Edition на сервере. Выполняется раз в 10 медленнее, поэтому гонял до 10000 циклов. Нет сбоев! Без разницы, закрываю Statement и ResultSet или нет. Работает и все тут (без всяких sleep()), хотя драйвер в программе тот же самый от XE Development. У кого-нибудь есть идеи??? Как вообще правильно программировать взаимодействие с базой?

Ответ

Насчет statement не уверен, но точно знаю, что с resultset будет. Кажется, предел находится на 32к незакрытых resultset, oracle не сможет возвращать результаты выборки. имхо, лучше всегда за собой убирать :)