Pentaho kettle Environment

Kettle requires a JDK environment. You can download it on the Oracle official website. In addition, JDBC or ODBC is required to use kettle. I prefer JDBC. I hate to understand the concept and knowledge of JDBC.

"What is JDBC?

JDBC (Java Data Base connectivity, Java database connection) is a Java API used to execute SQL statements. It can provide unified access to multiple relational databases, it consists of a group of classes and interfaces written in Java. JDBC provides a standard API for tools/database developers to build more advanced tools and interfaces so that database developers can use pure Java APIs to write database applications, JDBC is also a trademark name.

With JDBC, it is easy to send SQL statements to various relational data. In other words, with JDBC APIs, you do not have to write a program for accessing the Sybase Database, write another program for accessing the Oracle database, or write another program for accessing the Informix database, programmers only need to use the jdbc api to write a program. It can send SQL calls to the corresponding database. At the same time, the combination of Java and JDBC makes it unnecessary for programmers to write different applications for different platforms. They only need to write the program once to run it on any platform, this is also the advantage of "writing once and running everywhere" in Java.

The Java database connection architecture is a standard method for connecting Java applications to databases. JDBC is an API for Java programmers and an interface model for service providers that connect to databases. As an API, JDBC provides standard interfaces for program development and provides standard methods for database vendors and third-party middleware vendors to connect to databases. JDBC uses existing SQL standards and supports connection standards with other databases, such as bridging between ODBC. JDBC implements all these standard-oriented interfaces with simple, strictly-defined types, and high-performance implementations.

Java is a powerful, secure, easy to use, easy to understand, and can be automatically downloaded from the network. It is an outstanding language for writing database applications. All you need is the method of dialog between Java applications and different databases. JDBC serves as a mechanism for this purpose.

JDBC extends Java functions. For example, you can use Java and JDBC APIs to publish a webpage containing an applet, and the information used by the applet may come from a remote database. Enterprises can also use JDBC to connect all employees to one or more internal databases over the Intranet (even if these employees use different operating systems such as Windows, Macintosh, and UNIX ). As more and more programmers begin to use the Java programming language, the requirements for convenient access to the database from Java are also increasing.

MIS administrators like the combination of Java and JDBC because it makes information dissemination easy and economical. Enterprises can continue to use their installed databases and easily access information, even if the information is stored on different database management systems. The development period of new programs is very short. Installation and version control will be greatly simplified. A programmer can write an application only once or update it only once, and then put it on the server. then anyone can get the latest version of the application. For commercial sales information services, Java and JDBC can provide external customers with a better way to obtain information updates. "

 

"JDBCUsage

To put it simply, JDBC can do three things: establish a connection with the database, send SQL statements, and process the results. The following code snippet provides a basic example of the preceding three steps:

 

1 connection con = drivermanager. getconnection ("JDBC: ODBC: wombat", "login", "password ");
2 Statement stmt = con. createstatement ();
3 resultset rs = stmt.exe cutequery ("selecta, B, c from Table1 ");
4 While (Rs. Next ()){
5 Int x = Rs. getint ("");
6 string S = Rs. getstring ("B ");
7 float F = Rs. getfloat ("C ");
8}

 

 

The above Code makes a classic summary of JDBC-based database access. Of course, it will be analyzed in detail in the subsequent sections of this section. "

 

"JDBC API

JDBC is a "low-level" interface, that is, it is used to directly call SQL commands. In this regard, it provides excellent functionality and is easier to use than other database connection APIs, but it is also designed as a basic interface on which advanced interfaces and tools can be created. An advanced interface is a user-friendly interface. It uses an API that is easier to understand and more convenient. This API is converted to a low-level interface such as JDBC behind the scenes.

In the "Object/relationship" ing of the relational database, each row in the table corresponds to an instance of the class, and the value of each column corresponds to an attribute of the instance. As a result, programmers can directly operate on Java objects. The SQL calls required for data access will be automatically generated under "Mask. In addition, more complex ing can be provided, such as combining rows in multiple tables into a Java class.

With increasing interest in JDBC, more and more developers are using JDBC-based tools to make programming easier. Programmers have been writing applications that make it easier for end users to access databases. For example, an application can provide a menu for selecting database tasks. After a task is selected, the application provides a prompt and a blank space to fill in the information required to execute the selected task. Enter the required information and the application will automatically call the required SQL command. With the help of such a program, you can execute database tasks even if you do not understand the SQL syntax at all. "

 

"JDBC API

JDBC is a "low-level" interface, that is, it is used to directly call SQL commands. In this regard, it provides excellent functionality and is easier to use than other database connection APIs, but it is also designed as a basic interface on which advanced interfaces and tools can be created. An advanced interface is a user-friendly interface. It uses an API that is easier to understand and more convenient. This API is converted to a low-level interface such as JDBC behind the scenes.

In the "Object/relationship" ing of the relational database, each row in the table corresponds to an instance of the class, and the value of each column corresponds to an attribute of the instance. As a result, programmers can directly operate on Java objects. The SQL calls required for data access will be automatically generated under "Mask. In addition, more complex ing can be provided, such as combining rows in multiple tables into a Java class.

With increasing interest in JDBC, more and more developers are using JDBC-based tools to make programming easier. Programmers have been writing applications that make it easier for end users to access databases. For example, an application can provide a menu for selecting database tasks. After a task is selected, the application provides a prompt and a blank space to fill in the information required to execute the selected task. Enter the required information and the application will automatically call the required SQL command. With the help of such a program, you can execute database tasks even if you do not understand the SQL syntax at all. "

 

"JDBC URLParameter Details

Jdbc url provides a way to identify a database, so that the corresponding driver can identify the database and establish a connection with it. In fact, the driver programmer will decide what jdbc url to use to identify a specific driver. Users do not have to worry about how to form JDBC URLs; they only need to use the URLs provided with the driver used. The role of JDBC is to provide certain conventions that drivers programmers should follow when constructing their JDBC URLs.

Since JDBC URLs must be used together with various drivers, these conventions should be flexible. First, they should allow different drivers to use different schemes to name databases. For example, the ODBC sub-protocol allows (but is not required) URLs to contain attribute values.

Second, the jdbc url should allow the driver programmer to include all required information. In this way, you can enable the database connection of the applet that you want to talk to a given database without requiring you to perform any system management work.

Finally, JDBC URLs should allow some degree of directness. That is to say, the jdbc url can point to the logical host or database name, and the logical host or database name will be dynamically converted from the network naming system to the actual name. This prevents the system administrator from declaring a specific host as a part of the JDBC name. There are multiple network naming services (such as DNS, NIS, and DCE), but there is no restriction on which Naming Service to use.

The standard syntax of jdbc url is as follows. It consists of three parts separated by colons:

JDBC: <sub-Protocol> <sub-Name> the jdbc url can be divided into the following three parts:

(1) JDBC Protocol: the protocol in the jdbc url is always JDBC.

(2) <sub-Protocol>: name of the driver name or database connection mechanism (this mechanism can be supported by one or more drivers. A typical example of a sub-protocol name is "ODBC", which is specially reserved for URLs used to specify ODBC-style data resource names. For example, to access a database through a JDBC-ODBC bridge, you can use the URL: JDBC: ODBC: Book shown below. In this example, the sub-protocol is "ODBC" and the sub-name "book" is a local ODBC data resource. If you want to use the network Naming Service (the database name in the jdbc url does not need to be the actual name), the naming service can be used as a sub-protocol. For example, the following URL is available: JDBC: dcenaming: accounts. In this example, the URL specifies that the local DCE Naming Service should resolve the database name "accounts" to a more specific name that can be used to connect to the real database.

(3) <sub-Name>: A method used to identify a database. Sub-names can vary according to different sub-protocols. It can also have sub-names (containing any internal syntax selected by the driver programmer ). The sub-name is used to provide sufficient information for locating the database. Because ODBC provides the rest of the information, it is sufficient to use "book. However, databases on remote servers need more information. For example, if the database is accessed through the Internet, the network address should be included as a part of the sub-name in the jdbc url, and the following standard URL naming conventions must be observed: // host name: Port/sub-protocol. "

JDBC Driver Installation

Take mysqlto say, first download mysql-connector-java-5.1.15.zip, decompress the file and put the mysql-connector-java-5.1.15-bin.jar to (Java installation directory) c: \ Program Files \ Java \ jre6 \ Lib \ ext