Call three stored procedures jdbc

Question

Please tell me why it is

public class Main { private static final String URL = "jdbc:mysql://localhost:3306/test"; private static final String USERNAME = "root"; private static final String PASSWORD = "root"; public static void main(String[] args) { try { Driver driver = new FabricMySQLDriver(); DriverManager.registerDriver(driver); Connection connection = getConnection(URL, USERNAME, PASSWORD); } catch (SQLException e) { System.err.println("Не удалось загрузить класс драйвера!"); } public static void sel (Connection connection) { try (Statement statement = connection.createStatement()) { ResultSet resultSet = statement.executeQuery("{CALL sel}"); while (resultSet.next()) { System.out.println(resultSet.getInt(1) + " " + resultSet.getString(2)); } } catch (SQLException e) { e.printStackTrace(); } } public static void ins_upd (Connection connection, int ID, String NAME) { try { PreparedStatement pstmp = connection.prepareStatement("{CALL ins_upd(?,?)}"); pstmp.setInt(1, ID); pstmp.setString(2, NAME); System.out.println("Готово!"); } catch (SQLException e) { e.printStackTrace(); } } public static void del (Connection connection, int ID) { try { PreparedStatement pstmp = connection.prepareStatement("{CALL del(?)}"); pstmp.setInt(1, ID); System.out.println("Готово!"); } catch (SQLException e) { e.printStackTrace(); } } }

gives errors:

 Error26, 9) java: illegal start of expression Error26, 16) java: illegal start of expression Error26, 22) java: ';' expected Error26, 28) java: not a statement Error26, 31) java: ';' expected

It was conceived as methods for the three stored procedures for the web server, but I get them not designed so?

If you use any decent IDE, it will immediately show you that you do not have Java syntax.
Where does the body of a public static void main(String[] args) method end public static void main(String[] args) ?
The public static void sel (Connection connection) method declaration is part of a class or part of a main ?

Accepted Answer · 2016-12-13T07:28:45

I usually work with CallableStatement when calling stored procedures

I will give you a code snippet can help

 private static final String QUERY = "{? = call TEST(?,?,?,?,?,?)}"; try(CallableStatement cstmt = conn.prepareCall (QUERY)){ cstmt.registerOutParameter (1, Types.INTEGER); cstmt.setString(2, clientID); cstmt.setString(3, reqId); cstmt.setString(4, login); cstmt.setString(5, key); ...... cstmt.execute(); int ans = cstmt.getInt(1); }

UPDATE !!!

  public static void sel (Connection connection) { try(CallableStatement statement = connection.prepareCall("{CALL sel}")){ boolean hadResults = statement.execute(); while (hadResults) { try(ResultSet resultSet = statement.getResultSet()){ while (resultSet.next()) { System.out.println(resultSet.getInt(1) + " " + resultSet.getString(2)); } }catch (SQLException e) { // TODO: handle exception } hadResults = statement.getMoreResults(); } } catch(SQLException e){ e.printStackTrace(); } } public static void ins_upd (Connection connection, int ID, String NAME) { try(CallableStatement statement = connection.prepareCall("{CALL ins_upd(?,?)}")){ statement.setInt(1, ID); statement.setString(2, NAME); boolean hadResults = statement.execute(); ///// if stored procedure has result } catch(SQLException e) { e.printStackTrace(); } } public static void del (Connection connection, int ID) { try(CallableStatement statement = connection.prepareCall("{CALL del(?)}")){ statement.setInt(1, ID); boolean hadResults = statement.execute(); ///// if stored procedure has result } catch(SQLException e) { e.printStackTrace(); } }

Sorry, I still do not know the language well, maybe there is an example of a complete code with at least two procedures?
It depends on the logic of your proposal, but usually it should be divided into methods
Updated the answer to your code, if you have questions please contact
I want to make an html page in which you can call the desired procedure, do not tell me what to redo?
I was going to add the procedure call by method name in html, as in <form action = "main.java" method = "sel">, but it does not work.
If it doesn't bother you, you couldn’t write to drhev@yandex.ru mail

GarRudo GarRudo 18 6 · Answer 2 · 2016-12-13T00:55:19

Hello!

We are passing this semester JSF application. We were shown this method of connecting to DB. The mysql-connector-java-5.1.40-bin.jar to connect. Through the controllers we transfer requests to the user from the page in the DAO, where the request is processed and returned back to the controller as an object containing what was requested.

Maybe it will do?

Simple option:

 package ie.gmit.sw; import java.sql.*; import java.util.Scanner; import com.mysql.jdbc.exceptions.jdbc4.CommunicationsException; import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException; import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; public class Public_Holiday { public static void main(String[] args) { String name; try { MysqlDataSource mysqlDS = new MysqlDataSource(); mysqlDS.setURL("jdbc:mysql://localhost:3306/superheroes"); mysqlDS.setUser("root"); mysqlDS.setPassword(""); Connection connect = mysqlDS.getConnection(); Statement myStmt = connect.createStatement(); System.out.println("Enter superhero name: "); Scanner sc = new Scanner(System.in); name = sc.nextLine(); sc.close(); String query = "delete from superhero_table where name like '" + name + "'"; int rs = myStmt.executeUpdate(query); //System.out.println(rs); if(rs == 0){ System.out.println("Superhero with name '" + name + "' does not exist in DB!" ); } } catch (CommunicationsException communication_failure){ System.out.println(communication_failure.getMessage() + "\nCan not reach the server!"); } catch(MySQLIntegrityConstraintViolationException dependancy_deletion){ System.out.println(dependancy_deletion.getMessage() + "\nDelete superhero from any dependant tables first."); } catch( SQLException se){ //System.out.println(se.getMessage()); se.printStackTrace(); } } }

JSF connection option. In this case, we store the mysql-connector-java-5.1.40-bin.jar driver in the client-side lib directory. If you want, you can look at my repository https://github.com/EddyCodeIt/JSF-MySql-Application , see ideas.

 package ie.gmit.sw; import java.util.*; import java.sql.*; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; public class DAO { // mysqlDS object private DataSource mysqlDS; // DAO() constructor public DAO() throws Exception { Context context = new InitialContext(); String jndiName = "java:comp/env/jdbc/garage"; mysqlDS = (DataSource) context.lookup(jndiName); } /* Query Database for _____ details */ public ArrayList<Garage> getManufacturerDetails() throws SQLException{ // create new array list to store data from obtained from DB. Later we return this ArrayList ArrayList<Garage> manufacturers = new ArrayList<>(); // connect to database and prepare statement Connection conn = mysqlDS.getConnection(); PreparedStatement myStmt = conn.prepareStatement("select * from manufacturer"); ResultSet rs = myStmt.executeQuery(); while (rs.next()) { String manu_code = rs.getString("manu_code"); String manu_name = rs.getString("manu_name"); String manu_details = rs.getString("manu_details"); manufacturers.add(new Garage(manu_code, manu_name, manu_details)); } conn.close(); myStmt.close(); return manufacturers; }

Sorry if not the topic answered :)

And if you know that you are not answering the topic, then why do you answer at all?

m. vokhm m. vokhm 2,690 four 20 · Answer 3 · 2016-12-13T06:52:00

Because in the given text Java syntax is not observed. A method declaration cannot be part of the body of another method. In your case, the body (program code) of the main method smoothly goes into the declaration of the sel method, as if the declaration of the method is part of the code.

It turns out you just need to extend the sel, ins_upd, del methods from the main method?
In general, I would advise you to familiarize yourself with at least the basics of the language before writing work with a database or something else.
If your program is not simply compiled, and not out of carelessness (which sometimes happens with everyone), but due to ignorance of the most elementary foundations of the language, then it’s very premature to talk about some useful functionality of such a program.

Call three stored procedures jdbc

3 answers 3

More articles: