battle programmers alliance

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

Display results as :
 
Rechercher Advanced Search
Latest topics
» android mobile app dev APK
Yesterday at 10:01 pm by Moti Barski

» battle programmers alliance new features :
Yesterday at 5:25 pm by Admin

» urban prepper grimoire
Sat Aug 18, 2018 1:03 pm by Moti Barski

» broadcast receiver dead
Sat Aug 04, 2018 7:51 am by Moti Barski

» programming tool : find dif between 2 texts
Tue Jul 31, 2018 6:32 am by Moti Barski

» java class that filter numbers out of a string
Mon Jul 30, 2018 11:20 am by Moti Barski

» android studio execises
Mon Jul 09, 2018 1:43 pm by Moti Barski

» problem with firebase authentication
Thu Jun 28, 2018 9:52 am by Moti Barski

» text art canvas
Wed Jun 20, 2018 8:55 pm by Moti Barski

August 2018
SunMonTueWedThuFriSat
   1234
567891011
12131415161718
19202122232425
262728293031 
CalendarCalendar
Social bookmarking
Social bookmarking digg  Social bookmarking delicious  Social bookmarking reddit  Social bookmarking stumbleupon  Social bookmarking slashdot  Social bookmarking yahoo  Social bookmarking google  Social bookmarking blogmarks  Social bookmarking live      

Bookmark and share the address of battle programmers alliance on your social bookmarking website
Share | 
 

 java and MySQL

Go down 
AuthorMessage
Moti Barski

avatar

Posts : 177
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 Online
Admin
Admin
avatar

Posts : 113
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 : 113
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: