JDBC Database Connection

1. The third Way is the most recommended!

/** * Note that the database connected in 1.url must exist, such as Hello must exist, otherwise the error * 2.user and password is fixed, can not be written username or pass, otherwise error * @author Ckang * */public class Jdbcdemo {private String URL = "Jdbc:mysql://localhost:3306/hello"; String user = "root"; String password = "root"; @Testpublic void ConnectDB1 () throws exception{properties props = new Properties ();p Rops.setprop Erty ("user", user);p rops.setproperty ("password", password);D river Driver = new Com.mysql.jdbc.Driver (); Connection connect = driver.connect (URL, props); SYSTEM.OUT.PRINTLN (connect);} @Testpublic void ConnectDB2 () throws EXCEPTION{//1. Registered database driver, but mysql oracle sqlserverdriver mysqldriver = new Driver ();D Rivermanager.registerdriver (mysqldriver);//You can register multiple different database//2. Create a connection Connection connection = Drivermanager.getconnection (Url,user,password);//Add Hello with password and account, do not write here will error System.out.println (connection);} @Testpublic void ConnectDB3 () throws EXCEPTION{//1. Register the database driver and load the class information through bytecode. Because drivermanager.registerdriver are static methods Class.forName ("Com.mysql.jdbc.Driver");//load MySQL driver class DRIVER//2. Create a connection Connection connection = drivermanager.getconnection (URL, user, password); System.out.println (connection);}}

2. Create a table, execute a fixed SQL through statement, usually first test the SQL statement in the database

/** * CREATE TABLE student (id INT PRIMARY KEY auto_increment, NAME VARCHAR (), Gender VARCHAR (2)) DESC Studentdrop TABLE Student * @author Ckang * */public class Createdemo {private String URL = "Jdbc:mysql://127.0.0.1:3306/hello";p rivate Strin G user = "root";p rivate String password = "root"; @Testpublic void Create () throws exception{statement stmt = null; Connection conn = null;try {class.forname ("Com.mysql.jdbc.Driver"); conn = Drivermanager.getconnection (Url,user, password); stmt = Conn.createstatement (); String sql = "CREATE TABLE student (id INT PRIMARY KEY auto_increment, NAME VARCHAR (), Gender VARCHAR (2))"; Stmt.execute ( SQL);} catch (ClassNotFoundException e) {throw new RuntimeException (e);} Finally{if (stmt! = null) {Stmt.close ();} IF (conn! = null) {Conn.close ();}}}}

3. Extracting common classes, extracting methods to release resources and get connections

 Public classJdbcutils {Private StaticString URL ="Jdbc:mysql://localhost:3306/hello"; Private StaticString user ="Root"; Private StaticString Password ="Root"; Privatejdbcutils () {}Static{        Try{class.forname ("Com.mysql.jdbc.Driver");//1. Registering Drivers}Catch(Exception e) {Throw NewRuntimeException (e); }    }        //2. How to get a connection to a database     Public StaticConnection getconnection () {Try {            returndrivermanager.getconnection (URL, user, password); } Catch(Exception e) {Throw NewRuntimeException (e); }    }        //3. Ways to release resources     Public Static voidClose (Statement stmt, Connection conn) {if(NULL!=stmt) {            Try{stmt.close (); } Catch(Exception e) {Throw NewRuntimeException (e); }        }        if(NULL!=conn) {            Try{conn.close (); } Catch(Exception e) {Throw NewRuntimeException (e); }        }    }}

4. have been censored methods

public class Saveorupdate {@Testpublic void Save () {Connection conn = null; Statement stmt = null;try {conn = Jdbcutils.getconnection (); stmt = Conn.createstatement (); String sql = "INSERT into student (Name,gender) VALUES (' Yui Hatano sister ', ' female ')"; int count = stmt.executeupdate (SQL); System.out.println (count);} catch (Exception e) {throw new RuntimeException (e);} Finally{jdbcutils.close (STMT, conn);}} Note: 1.  Update table student error, can only write update student!!!! such as: "UPDATE student SET name= ' Yao Yao ', gender= ' where id = ' 2 ' and name= ' Zhangsan '"//2. Set multiple values can only be separated by commas and cannot be separated by and!!!! @Testpublic void Update () {Connection conn = null; Statement stmt = null;try {conn = Jdbcutils.getconnection (); stmt = Conn.createstatement (); String sql = "UPDATE student SET name= ' I love Yao Yiu ', gender= ' where ID =2"; stmt.executeupdate (sql);} catch (Exception e) {throw new RuntimeException (e);} Finally{jdbcutils.close (STMT, conn);}} @Testpublic void Delete () {Connection conn = null; Statement stmt = null;try {conn = Jdbcutils.getconnection (); stmt = conn.Createstatement (); String sql = "Delete from student WHERE ID =2"; stmt.executeupdate (sql);} catch (Exception e) {throw new RuntimeException (e);} Finally{jdbcutils.close (STMT, conn);}} Public list<student> FindAll () {Connection conn = null; Statement stmt = null;try {conn = Jdbcutils.getconnection (); stmt = Conn.createstatement (); String sql = "SELECT * from STUDENT"; ResultSet ResultSet = stmt.executequery (sql); list<student> studentlist = new arraylist<> (); while (Resultset.next ()) {Student Student = new Student (); Student.setid (Resultset.getint ("id"));//The column name must be the same as the database field but not case-sensitive student.setname (resultset.getstring ("name")); Student.setgender (resultset.getstring ("GeNdEr")); Studentlist.add (student);} return studentlist;} catch (SQLException e) {throw new RuntimeException (e);}} @Testpublic void Iterate () {list<student> studentlist = FindAll (); for (Student stu:studentlist) { System.out.println (Stu.getid () + "* * *" +stu.getname () + "* * * *" +stu.getgender ());}} Class Student{private int Id;private String Name;private string Gender;public Student () {}public int getId () {return ID;} public void setId (int id) {this.id = ID;} Public String GetName () {return name;} public void SetName (String name) {this.name = name;} Public String Getgender () {return gender;} public void Setgender (String gender) {This.gender = gender;} @Overridepublic String toString () {return "Student [id=" + ID + ", name=" + name + ", gender=" + Gender + "]";}}

　　

JDBC Database Connection