battle programminga forum for elite programmerswith extreme will powerto sharpen theire skills
 
HomeCalendarFAQSearchMemberlistUsergroupsRegisterLog in
Share | 
 

 java and MySQL

Go down 
AuthorMessage
Moti Barski

avatar

Posts : 238
Join date : 2011-08-02

PostSubject: java and MySQL   Sun Apr 15, 2018 8:57 pm

mySQL the database
mySQL workbench : a GUI for CRUDing sql DBs
connectorJ : enables java platforms to work with sql DBs

mySQL installer : a package with the above 3

DL said installer from : https://dev.mysql.com/downloads/installer/
choose the community DL (mySql-installer-community-5.7.2.1.msi) and fire it up.

on the wizard choose: custom, mySQL server 5.7_mySQL server 5.7.21 - x64 + applications mySql workbench,
standalone mtSql server/classic, config type : development machine tcp/ip open +firewall+ port 3306,
invent pass, config mysql as win service + start server at sys startup + run service as standard

in the workbench creating a DB and table in it is right click intuitive.
PK : primary key FK (foriegn key): the PK parallal in a 2nd DB. therefor an FK can not be null.
those keys are used for join methods which are used for displaying the fusion between DBs.

you need to refresh after queries there is a refresh button in the SQL explorer window near the DB.

vars

https://www.w3schools.com/sql/sql_datatypes.asp

aggerate functiokns
https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

MySQL query conjurations :

select * from testdb1.testtable;
select * from databeName.someTableName;

--this is a comment

http://cse.unl.edu/~sscott/ShowFiles/SQL/CheatSheet/SQLCheatSheet.html
mysql aggregate functions :
http://www.mysqltutorial.org/mysql-aggregate-functions.aspx

stored methodes :

A stored procedure is a set of SQL statements that can be stored in the server.
1 situations where stored routines can be particularly useful:
2 when security is paramount

When the routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). USE statements within stored routines are disallowed.
You can qualify routine names with the database name. This can be used to refer to a routine that is not in the current database. For example, to invoke a stored procedure p or function f that is associated with the test database, you can say CALL test.p() or test.f().
When a database is dropped, all stored routines associated with it are dropped as well.


strored methodes

http://www.mysqltutorial.org/mysql-stored-function/

http://www.cmi.ac.in/~madhavan/courses/databases10/mysql-5.0-reference-manual/stored-procedures.html

join queries

https://www.w3schools.com/sql/sql_join.asp
https://dev.mysql.com/doc/refman/8.0/en/join.html

ready made example DB for practice : northwind :
run this query (in the mysql workbench) to create it :
https://github.com/jpwhite3/northwind-MySQL/blob/master/northwind.sql
run this query to populate it with data
https://github.com/dalers/mywind/blob/master/northwind-data.sql

🐷

_________________
MB over and out _______________________     


Last edited by Moti Barski on Mon Apr 23, 2018 12:08 am; edited 2 times in total
Back to top Go down
View user profile
Admin
Admin
avatar

Posts : 120
Join date : 2011-08-01

PostSubject: Re: java and MySQL   Sat Apr 28, 2018 11:35 am



get this jar file :

mysql-connector-java-5.1.46.jar

create new java project

create packages
DAL : data access layer where you put the sql codes
NTW query sql are the select codes, non query are the CRUD codes that modify the DB
BLL : buisness logic layer where you get the user input ready and readable for the DAL tier (3rd tierS)
PL : presentation layer - main class

create a new folder in said java project : right click project in the package explorer, new, folder
(I named it JARs), next copy the mysql-connector-java-5.1.46.jar file into said folder.


right click the project in the package explorer, build path, configure build path
in the libraries tab click add JARs, choose mysql-connector-java-5.1.46.jar. finally
go to the order and export tab, check the mysql-connector-java-5.1.46.jar, click on apply and close btn.

http://www.vogella.com/tutorials/MySQLJava/article.html
in the PAL package (the one where all the special spl codes are) : right click, new, file, I called it
dbinfo.properties. add text to in said file :

DBDRIVER=com.mysql.jdbc.Driver
DBURL=jdbc:mysql://localhost:3306/feedback?useSSL=false
DBUSER=sqluser
DBPWD=sqluserpw


for extra users add :
DBUSER1=someuser
DBPWD1=password1
DBUSER2=somex
DBPWD2=password2

using a file allows you flexibility for replacing DBs in case of need.
when your project is online the user and pass are the ones you get from the host server.
the url is your domain

in the MySQLAccess.java class change :
Code:
connect = DriverManager .getConnection("jdbc:mysql://localhost/feedback?"+"user=sqluser&password=sqluserpw");

to get the dbinfo.properties path right click it, properties.

to :
Code:

Properties properties = new Properties();
         FileInputStream inputStream = new FileInputStream(
               "C:\\Users\\Lenovo\\eclipse-workspace\\jdb\\src\\de\\vogella\\mysql\\first\\dbinfo.properties");
         properties.load(inputStream);
         connect = DriverManager.getConnection(properties.getProperty("DBURL"), properties.getProperty("DBUSER"),
               properties.getProperty("DBPWD"));

_________________
kurayami no kagi  _____    
Back to top Go down
View user profile http://yotamarker.forumotion.com
Admin
Admin
avatar

Posts : 120
Join date : 2011-08-01

PostSubject: Re: java and MySQL   Mon Apr 30, 2018 3:02 pm

using java eclipse oxygen to call an SQL stored procedure :

Code:

public String useStoredProcedure(){
   try
   (CallableStatement callable=connection.prepareCall("(call storedProcedureName(?,?))")){
      callable.setInt(1,4);
      callable.registerOutParameter(2,java.sql.Types.VARCHAR);
      callable.execute();
      String name = callable.getString(2);
      return name;
}catch(SQLException e){e.printStackTrace();}
}

the stored procedure used signature :
PROCEDURE `storedProcedureName`(in x int, out y varchar(50))

_________________
kurayami no kagi  _____    
Back to top Go down
View user profile http://yotamarker.forumotion.com
 
java and MySQL
Back to top 
Page 1 of 1
 Similar topics
-
» Verify command when used with Java,JUNIT and Eclipse
» opera browser not loading the page using selenium Rc(Java)
» JAVA tunnel Based UAE Solution Available in Just $149 @ VoIP
» [Help] Java input Username field as a timestamp
» LocalFileDetector (uploading files) - InternetExplorer Driver (Java platform)

Permissions in this forum:You cannot reply to topics in this forum
battle programmers alliance :: battle programming alliance :: battle programming-
Jump to: