Connect to the MySQL database using Java; read and write Excel tables using Java

Connect to the mysql database using java:

Because we have been using the XAMPP environment tool before, and then downloaded a WAMP to learn how to use PHP.

Both of them integrate the MYSQL database and click start MYSQL directly. This is very simple and convenient, and their visual interfaces are the same.

Take XAMPP as an example. After you click start MYSQL,

Click "Admin" on the MYSQL line to open http: // localhost/phpmyadmin/to view the database. (If the database cannot be opened, enable Apache and try again ).

You can click the Shell call-up terminal on the right, enter mysql, and press enter to operate the database using statements. Of course, you can also operate on the visual interface above.

Open eclipse. To connect java to mysql, you need a jar package at http://dev.mysql.com/downloads/connector/j/to download to the latest version.

After the download, extract and add $ {your_PATH}/mysql-connector-java-5.1.24/mysql-connector-java-5.1.24-bin.jar to the environment variable. Import the jar package to the java project library.

Create a database named mytest and create a table named student. The table structure is as follows:

The java code is as follows:

Import Java. SQL. *; public class jdbctest {public static void main (string [] ARGs) {// 1. register the driver try {class. forname ("com. mySQL. JDBC. driver ");} catch (classnotfoundexception ex) {ex. printstacktrace ();} // declare the variable, use it, and then close connection conn = NULL; // database connection statement stmt = NULL; // database expression resultset rs = NULL; // result set try {// 2. obtain the database connection conn = drivermanager. getconnection ("JDBC: mysql: // localhost: 3306/mytest", "Root", ""); // 3. obtain the expression stmt = Conn. createstatement (); // insert data stmt.exe cuteupdate ("insert into student (username, password, age) values ('ted', '123', 25 )"); // 4. run SQL rs = stmt.exe cutequery ("select * from student"); // 5. while (RS. next () {system. out. println ("No. =" + Rs. getint (1); system. out. println ("name =" + Rs. getstring ("username"); system. out. println ("Password =" + Rs. getstring ("password ")); System. out. println ("age =" + Rs. getstring ("Age"); system. out. println ("---------------") ;}} catch (exception ex) {ex. printstacktrace ();} finally {try {If (RS! = NULL) {Rs. Close () ;}if (stmt! = NULL) {stmt. Close ();} If (Conn! = NULL) {conn. Close () ;}} catch (exception ex) {ex. printstacktrace ();}}}}

========================================================== ========================================================== ======================================

Java reads an excel table:

It should have been a complicated process, but there is something in the world called open-source class libraries ~ The operations on excel become very simple.

Download jxl. jar and import it to the project.

Create a new dataanalysisfolder under the e-disk, and click the new text.xls folder. Note that XLS is not XLSX. This class library seems to support only the old Excel version. How to solve the new version is still under research.

The Java code is as follows:

Import Java. io. file; import Java. io. ioexception; import jxl. cell; import jxl. sheet; import jxl. workbook; import jxl. read. biff. biffexception; import jxl. write. label; import jxl. write. writablesheet; import jxl. write. writableworkbook; public class exceltest {public void write () throws exception {writableworkbook WWB = NULL; // create a writable Excel Workbook string filename = "E:" + file. separator + "dataanalysis" + file. separator + "test.xls"; // create a workbook WWB = workbook with filename as the file name. createworkbook (new file (filename); // create a worksheet writablesheet Ws = WWB. createsheet ("test shee 1", 0); // The row number of the Excel table to be inserted. Int row starts from 0 by default; // The column number of the Excel table to be inserted. The default value is label labelc = new label (3, 5, "QQ") from 0. Label Labled = new label (5, 3, "DD"); ws. addcell (labelc); ws. addcell (Labled); // write it into the document WWB. write (); // close the Excel Workbook object WWB. close ();} public static void main (string ARGs []) throws exception {file F = new file ("E:" + file. separator + "dataanalysis" + file. separator + "test.xls"); // This is created by myself and is located in test.xls exceltest ET = new exceltest (); et. write (); try {workbook book = workbook. getworkbook (f); // sheet = book. getsheet (0); // obtain the first worksheet object for (INT I = 0; I <sheet. getrows (); I ++) {for (Int J = 0; j <sheet. getcolumns (); j ++) {Cell cell = sheet. getcell (J, I); // obtain the cell system. out. print (cell. getcontents () + "");} system. out. print ("\ n") ;}} catch (biffexception e) {// todo auto-generated Catch Block E. printstacktrace ();} catch (ioexception e) {// todo auto-generated Catch Block E. printstacktrace ();}}}