Introduction to PostgreSQL Novice

PostgreSQL is an open-source database that has been widely used since MySQL was acquired, and it is a bold and innovative idea that companies in the market choose to use it, and PostgreSQL has many database features such as Orcale's sequence, There are also many business databases that do not have data types such as geometry type, IP type, and also support views, triggers, database constraints. If you think that MySQL is too monotonous to try PostgreSQL, there must be a lot of gains.

A PostgreSQL database and installation of the client

PostgreSQL is: http://www.postgresql.org/download/

The Pgadmin is: http://www.pgadmin.org/download/

In general, PostgreSQL can be installed successfully, if the installation can not be installed or after the service will not start, it is possible to check the computer is not installed MySQL cluster DB cluster, if installed before uninstalling, restart the computer after the installation is no problem, As for the Pgadmin point, the next step is to install successfully.

Simple use of two pgadmin

1 Connecting the database

Fill in the appropriate information, click OK

2 creation of databases and tables

You can also add primary keys, foreign keys, etc. using the client tool

3 Adding users

Three use JDBC to manipulate PostgreSQL

Preparation: Pgsql's JDBC Development kit (JAR package)-------cannot be missing

1 JDBC Tool classes

Package Org.lxh;import Java.sql.*;import Javax.naming.context;import Javax.naming.initialcontext;public class DBManager {public static final string dbdriver = "Org.postgresql.Driver";p ublic static final String dburl = "Jdbc:postgres Ql://localhost/mypgsql ";p ublic static final String DBUSER =" Postgres ";p ublic static final String dbpass =" root ";p rivate Connection conn = null;public Connection creatconn () {try {class.forname (dbdriver); conn = Drivermanager.getconnection ( Dburl, DBUSER, Dbpass); return conn;} catch (Exception Fe) {System.err.println ("Creatconn ():" + fe.getmessage ()); return null;}} public void Release () throws SQLException {if (conn! = null) {Conn.close ();}}}

2 CRUD Operations

Package Org.lxh;import static Org.junit.assert.*;import Java.sql.connection;import java.sql.PreparedStatement; Import Java.sql.resultset;import Java.sql.sqlexception;import Org.junit.test;public class Myjunit {/** Insert data * @throws Exception */@Testpublic void Testinsert () throws Exception {DBManager d=new DBManager ();    Connection Conn=d.creatconn ();    String sql= "INSERT into student (Name,password) VALUES (?,?)";    PreparedStatement pstmt=conn.preparestatement (SQL);    Pstmt.setstring (1, "Lin Chi-ling");    Pstmt.setstring (2, "123456");    int flag=pstmt.executeupdate ();    if (flag>0) {System.out.println ("Data insertion succeeded");    }else{System.out.println ("Data insertion failed"); } pstmt.close ();d. Release ();} /** Delete Record * @throws Exception */@Testpublic void Testdelete () throws Exception {DBManager d=new DBManager ();    Connection Conn=d.creatconn ();    String sql= "Delete from student where id=?";    PreparedStatement pstmt=conn.preparestatement (SQL);    Pstmt.setint (1, 0);    int flag=pstmt.executeupdate (); if (flag>0) {System.out.println ("Data deletion succeeded");    }else{System.out.println ("Data deletion failed"); } pstmt.close ();d. Release ();} /** Modify Record * @throws Exception */@Testpublic void Testupdate () throws Exception {DBManager d=new DBManager ();    Connection Conn=d.creatconn (); String sql= "Update student set name=?    where id=? ";    PreparedStatement pstmt=conn.preparestatement (SQL);    Pstmt.setstring (1, "Su has Friends");    Pstmt.setint (2, 2);    int flag=pstmt.executeupdate ();    if (flag>0) {System.out.println ("Data modification succeeded");    }else{System.out.println ("Data modification failed"); } pstmt.close ();d. Release ();} /** Query * @throws Exception */@Testpublic void Testquery () throws Exception {DBManager d=new DBManager ();    Connection Conn=d.creatconn ();    String sql= "SELECT * from student";    PreparedStatement pstmt=conn.preparestatement (SQL);    ResultSet Rs=pstmt.executequery ();    while (Rs.next ()) {System.out.println ("Name:" +rs.getstring ("name") + ", Password:" +rs.getstring ("password")); } pstmt.close ();d. Release ();}}

Note: PostgreSQL is case sensitive, the table, view, sequence commands to use lowercase, and do not use keywords such as user.

Introduction to PostgreSQL Novice