Description of MySQL

Properties of MySQL

  • simple database
  • its in xampp
  • "drivers" for C
  • "drivers" for C++
  • "drivers" for Excel
  • "drivers" for Visual Studio / Net
  • "drivers" for PHP
  • "drivers" for Python
  • "drivers" for JSP / Java
  • link

How get a MySQL database?

01) Start with xampp
02) start "C:\xampp\xampp-control.exe"
03) click on the button "Start" in the row "Apache"
04) click on the button "Start" in the row "MySQL"
05) click on the button "Admin" in the row "MySQL"
06) Image: MySQL01.jpg
07) Click on the button "New"
08) write the name of the database: Amacon
09) click on tjhe button "create"
10) Image: MySQL02.jpg
      Now you have a new database
11) You can insert new tables with the user interface
12) Image: MySQL03.jpg
13) But its easier, to insert the complete sql-statements
14) sql-statements (this work with auto-increment)
15) select the register "SQL"
16) insert the sql-statements and click the button "go"
17) insert the sql statement "select * from customer" and click the button "go"


Sequence

The autoincrement-statement are fine, but in some situation one need the primary key for a second insert.
The second database define a sequence.
01) Start with xampp
02) start "C:\xampp\xampp-control.exe"
03) click on the button "Start" in the row "Apache"
04) click on the button "Start" in the row "MySQL"
05) click on the button "Admin" in the row "MySQL"
06) Image: MySQL01.jpg
07) Click on the button "New"
08) write the name of the database: Amacon
09) click on tjhe button "create"
10) Image: MySQL02.jpg
Now you have a new database
11) You can insert new tables with the user interface
12) Image: MySQL03.jpg
13) But its easier, to insert the complete sql-statements
14) sql-statements-sequence (this work without auto-increment)
15) select the register "SQL"
16) insert the sql-statements and click the button "go"
17) insert the sql statement "select * from customer" and click the button "go"
18) if you want to insert a customer, we need the primary key.
19) in PHP you need this


How can I create a JSP-Project?

01) At first create a database with at least one datatable
02) Download the *.jar driver
link
03) You case use a special linux system or use the "Platform Independent" for Windows or Apple
04) Save the gz or zip file
05) Unpack the ZIP file and copy the file "mysql-connector-java-8.0.11.jar" to the lib directory
C:\xampp\tomcat\lib\
Now every project can use the jar-file.
Or you can copy the jar file to a WEB-INF directory
06) Start with xampp
07) start "C:\xampp\xampp-control.exe"
08) click on the button "Start" in the row "Apache"
09) click on the button "Start" in the row "MySQL"
10) click on the button "Start" in the row "Tomcat"
11) Perhaps you must change the ports
12) then you must change it in the server.xml (button Config)
13) AND in the dialog button "Config", button "Service and Prot Setting"
14) create the directory "mysql" in C:\xampp\tomcat\webapps\ROOT\mysql
15) create a jsp-file in the directory "mysql"
16) name of the file: "firstdbs.jsp"
16) insert the sample
17) start the jsp file with: http://localhost:8080/mysql/firstdbs.jsp


The server time zone value is unrecognized

Sometimes the jdbc driver don't recognized the correct timezone.
 
java.sql.SQLException: The server time zone value 'Westeuropäische Zeit' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

Solution

  • String dbUrl1 ="jdbc:mysql://localhost:3306/";
  • String dbUrl2 ="?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
  • String url = dbUrl1+database+dbUrl2;
  • cn = DriverManager.getConnection(url , user, passwd);
  • complete file

SQL-Commands

load:
Class.forName( dbdrv );
String url = dbUrl1+database+dbUrl2;
cn = DriverManager.getConnection(url , user, passwd);
st = cn.createStatement();
rs = st.executeQuery( "SELECT * FROM table" );

update/delete:
Class.forName( dbdrv );
String url = dbUrl1+database+dbUrl2;
cn = DriverManager.getConnection(url , user, passwd);
st = cn.createStatement();
int n = st.executeUpdate( sql ); // numbers of the results, if n > 0 then success


Debug print

This function is useful for the "debugging", when one can use out.println.

void print(String filename, String content) {
try {
FileOutputStream fout = new FileOutputStream(filename);
PrintStream pout = new PrintStream (fout);
pout.println(content);
pout.close();
fout.close();
}
catch (IOException e) {
}
} // print


createTable

This function create a html table with a JSon field!


function createTable(objs, numberDefinition, pindexnr, str_calledit, str_calldelete) {
let nl = '\n';
let obj = objs[0];
let captions = Object.keys(obj);

let s='';
s +='<table>' + nl;
s +='<tr>' + nl;
if (str_numberDefinition.length>0) {
s +=' <th>' + str_numberDefinition + '</th>' + nl;
}
for(var i in captions){
let scaption = captions[i];
let sfirst = captions[i].substring(0,1).toUpperCase();
s +=' <th>' + sfirst + scaption.substring(1,scaption.length) + '</th>' + nl;
}
if (str_calledit!='') {
s +=' <th>' + '-' + '</th>' + nl;
}
if (str_calldelete!='') {
s +=' <th>' + '-' + '</th>' + nl;
}
s +='</tr>' + nl;

for (let i in objs) {
s +='<tr>' + nl;
if (str_numberDefinition.length>0) {
s +=' <td>' + (i+1) + '</td>' + nl;
}
let obj = objs[i];
for(var j in obj){
//alert(j);
s +=' <td>' + obj[j] + '</td>' + nl;
}
if (str_calledit!='') {
s+=' <td class="tdbutton">' + '<input type="button" value="Edit" onclick="'+str_calledit+'(' + obj[pindexnr] + ')"/>' + '</td> ' + nl;
}
if (str_calldelete!='') {
s+=' <td class="tdbutton">' + '<input type="button" value="Delete" onclick="'+str_calldelete+'(' + obj[pindexnr] + ')"/>' + '</td> ' + nl;
}

s +='</tr>' + nl;
}

s +='</table>' + nl;
return s;
}