java and MySQL

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



Last edited by Moti Barski on Mon Apr 23, 2018 12:08 am; edited 2 times in total

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 :


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

to :

using java eclipse oxygen to call an SQL stored procedure :



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